By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Need assistance with this Excel MACRO in ACCESS

P: 13
'actually I have a macro that exports multiple querys or table into many excel spreadsheet with multiple worksheets.
'but this is just to test the Excel Macro to work on the exported files.
'is there any way to create just one RunCode that will work with for all of these files in a ACCESS Macro?

Expand|Select|Wrap|Line Numbers
  1. Function testfunction()
  2.  
  3. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Customers", "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx", True
  4.  
  5.    Dim XL As Object
  6.  
  7.     Set XL = CreateObject("Excel.Application")
  8. 'XL.Workbooks.Open "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx"
  9.  
  10.    XL.Workbooks.Open "C:\Users\Juan\Documents\acom split.xlsm"
  11.  
  12.    'what do I need to type in order for this macro to work on the transferspreadsheets? because it runs on itself.
  13.  
  14.      XL.Run "acom split.xlsm!acomsplit"
  15.  
  16.            End Function
What this Excel Macro does is split the worksheets based on the first column starting in Cell A2 into tabs. if there is anyway to do this in Access than relying on Excel Macro that would be greatly appreciated.
Dec 19 '11 #1
Share this Question
Share on Google+
7 Replies


P: 13
I thought I was getting better but it failed after several research. What is wrong with the code below? The error messages in RunCode says that it cant compile. What this Excel Macro does is split the worksheets based on the first column starting in Cell A2 into tabs. if there is anyway to do this in Access than relying on Excel Macro that would be greatly appreciated.
Expand|Select|Wrap|Line Numbers
  1. Function runmacro()
  2.  Dim xl As Object
  3.  Set xl = CreateObject("Excel.Application")
  4.  
  5.  With Excel.Application
  6.  .Visible = True
  7.  .Workbooks.Open "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx"
  8.  .Workbooks.Open "C:\Users\Juan\Documents\Acom Split.xlsm"
  9.  .Windows("C:\Users\Juan\Documents\Acom Split.xlsm").Visible = True
  10.  .Run "Acom Split.xlsm!ACOMSplit"
  11.  .Windows("C:\Users\Juan\Documents\Acom Split.xlsm").Visible = False
  12.  .ActiveWorkbook.Close savechanges:=True
  13.  .Workbooks("Acom Split.xlsm").Close False
  14.  
  15.  
  16. .Quit
  17.  End With
  18.  
  19.  Set xl = Nothing
  20.  
  21.  End Function
  22.  
Dec 19 '11 #2

NeoPa
Expert Mod 15k+
P: 31,261
PreciousLife73:
'is there any way to create just one RunCode that will work with for all of these files in a ACCESS Macro?
This is the only thing you posted that looks like a question. I have no idea what you mean by a RunCode, but it is certainly possible for a single piece of code in an Access project to work on multiple Excel files.

You include so little helpful information that it's hard to post anything that may be more helpful to you at this stage. You should understand that a piece of code isn't a question. The question is (or in this case perhaps is not) in the text.
Dec 19 '11 #3

P: 13
What I meant by RunCode is the functions that run the module. Alright, given the logic that I have provided in the thread. You stated that one piece of code "can" work with multiple excel. I am more interested in learning this as well. My understanding is based on your statement, ACCESS can open all Excel spreadsheets in a folder and perform the Excel Macro on each of them, correct? You did provide me the automation and the references has already been ticked. It is not really working. But i would like to change the script to make it more generic by identifying the folder rather than specific spreadsheet. Lets refer back to the VB that I just posted, why it is not firing the function name runmacro() in RunCode portion of the MACRO? It state that the function has error compiling. At first i thought maybe the reference tool was not checked but it is.
Dec 20 '11 #4

P: 13
I actually got it to work! See code below. However two questions, rather than running the macro on specified Excel file, what do i need to type to get it to work in all excel files in a folder? Question number two, sometimes I have a blank worksheet called Sheet1 with no data, and the next tab has a sheetname with data, the issue is the code is not looping through the entire workbook and skipping any blanks.
Expand|Select|Wrap|Line Numbers
  1. Function RunMacro()
  2.   Dim objExcel As Object
  3.   Set objExcel = CreateObject("Excel.Application")
  4.  
  5.   With objExcel
  6.     .Workbooks.Open "C:\Users\Juan\Documents\Acom Split.xlsm"
  7.     .Workbooks.Open "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx"
  8.     .Run "'ACOM Split.xlsm'!ACOMSplit"
  9.     .ActiveWorkbook.Close savechanges:=True
  10.   End With
  11.  
  12.   Set objExcel = Nothing
  13.  
  14. End Function
Dec 20 '11 #5

NeoPa
Expert Mod 15k+
P: 31,261
Let's go back to the start. We need a single question, explained clearly. You have now thrown much explanation but very little of it clear, and what seems to be the case is that you had problems (which you don't specify) compiling but you sorted those out and now you'd like to ask two more.

I haven't even caught up with the original question yet. Furthermore, only one question may be asked per thread. I can't even tell if the two follow-up questions should be allowed as a clarification of the first question as that's not even clear enough.

Moving on to processing multiple files from a single folder is not something I even want to start with you as it requires an understanding of the importance of clarity and clear understanding that you are illustrating mean little to you. You need to get the basics of asking a question clearly right before I'll even consider going there. Does that make sense?
Dec 21 '11 #6

P: 13
I understand. I would like to consider this matter closed. I was able to resolve the two questions. I will make sure I am more clear in any future inquiries. Thank you so much for bearing with me.
Dec 21 '11 #7

NeoPa
Expert Mod 15k+
P: 31,261
I understand how difficult many people find it to express themselves, and especially anything technical, clearly. I make the point (and often repeat it), not to criticise you, but because I also understand how much benefit you can reap from developing the skill.

Anyway, I'm glad you've got yourself a solution and wish you the best of luck with your project :-)
Dec 21 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.