Need assistance with this Excel MACRO in ACCESS

[preciouslife73]

'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?

Function testfunction()
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Customers", "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx", True
 
   Dim XL As Object
 
    Set XL = CreateObject("Excel.Application")
'XL.Workbooks.Open "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx"
 
   XL.Workbooks.Open "C:\Users\Juan\Documents\acom split.xlsm"
 
   'what do I need to type in order for this macro to work on the transferspreadsheets? because it runs on itself.
 
     XL.Run "acom split.xlsm!acomsplit"
 
           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.

[preciouslife73]

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.

Function runmacro()
 Dim xl As Object
 Set xl = CreateObject("Excel.Application")
 
 With Excel.Application
 .Visible = True
 .Workbooks.Open "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx"
 .Workbooks.Open "C:\Users\Juan\Documents\Acom Split.xlsm"
 .Windows("C:\Users\Juan\Documents\Acom Split.xlsm").Visible = True
 .Run "Acom Split.xlsm!ACOMSplit"
 .Windows("C:\Users\Juan\Documents\Acom Split.xlsm").Visible = False
 .ActiveWorkbook.Close savechanges:=True
 .Workbooks("Acom Split.xlsm").Close False
 
 
.Quit
 End With
 
 Set xl = Nothing
 
 End Function
 

[NeoPa]

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.

[preciouslife73]

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.

[preciouslife73]

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.

Function RunMacro()
  Dim objExcel As Object
  Set objExcel = CreateObject("Excel.Application")
 
  With objExcel
    .Workbooks.Open "C:\Users\Juan\Documents\Acom Split.xlsm"
    .Workbooks.Open "C:\Users\Juan\Desktop\test\Macrofolder\combine.xlsx"
    .Run "'ACOM Split.xlsm'!ACOMSplit"
    .ActiveWorkbook.Close savechanges:=True
  End With
 
  Set objExcel = Nothing
 
End Function

[NeoPa]

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?

[preciouslife73]

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.

[NeoPa]

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 :-)