Hi,
I have a project where I am copying data from one workbook to another using VBA.
The syntax has data copying from one workbook, opening another workbook, pasting, then save, close.
The thing is that the other workbook is visible when it opens, and saves then closes. I want it to be doing this in the background. i tried a couple of criteria, but it hides the workbook completely, and to open it you need to manually unhide it.
Attached is the project.
For an overview, the user enters information in the calculator, presses a button which copies the info, opens an email, copies it to the body, then opens another workbook, and copies in a table under the last line.
You have to open the file in another excel instance.
Dim xlApp as Excel.Application
set xlApp =New Excel.Application
To open the file in this excel instance:
set wb=xlapp.Workbooks.Open(filename)
Catalin Bombea said
You have to open the file in another excel instance.Dim xlApp as Excel.Application
set xlApp =New Excel.Application
To open the file in this excel instance:
set wb=xlapp.Workbooks.Open(filename)
Thank you Catalin,
so instead of using the one below I use the one you posted?
Sub OpenWorkbook()
Workbooks.Open "F:Department FoldersAccountingGMD Calculator JournalGMD_Calculator_Journal.xlsx"
End Sub
Its that when I get down to the below copy past code, I get a debug error
Set wsCopy = Workbooks("GMD Fee Calculator V03.xlsm").Worksheets("Lookup")
Set wsDest = Workbooks("GMD_Calculator_Journal.xlsx").Worksheets("Sheet1")
One of the files is open in another instance of excel.
xlApp.Workbooks.Open "F:Department FoldersAccountingGMD Calculator JournalGMD_Calculator_Journal.xlsx"
Set wsCopy = Workbooks("GMD Fee Calculator V03.xlsm").Worksheets("Lookup")
Set wsDest = xlApp.Workbooks("GMD_Calculator_Journal.xlsx").Worksheets("Sheet1")
If the excel application is missing, the code will associate the workbook with the same instance of the current workbook.
