Log In
Register
Home
May 11, 2019
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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)
May 11, 2019
Offline
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 Folders\Accounting\GMD Calculator Journal\GMD_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")
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
One of the files is open in another instance of excel.
xlApp.Workbooks.Open "F:\Department Folders\Accounting\GMD Calculator Journal\GMD_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.
1 Guest(s)
