Michael Vasilevsky
asked on
Reference Excel Spreadsheet with VBA
I'm using the below code to mess with an open Excel spreadsheet in VBA. It works fine unless my file rpt_OverviewforExport.xls is not the only Excel file open. How to make this more robust?
Thanks,
mv
Dim gobjExcel As Excel.Application
Dim WS As Excel.Worksheet
Set gobjExcel = GetObject(, "Excel.Application")
Set WS = gobjExcel.Workbooks("rpt_O verviewfor Export.xls ").Sheets( 1)
Thanks,
mv
Dim gobjExcel As Excel.Application
Dim WS As Excel.Worksheet
Set gobjExcel = GetObject(, "Excel.Application")
Set WS = gobjExcel.Workbooks("rpt_O
ASKER
Well I open the workbook programmatically so the problem is not that it's closed, the problem is that if the user has another instance of Excel running I get a "Subscript out of range" error.
Any good way to handle that? Maybe I'm just referencing the workbook I'm interested in wrong?
Thx,
mv
Any good way to handle that? Maybe I'm just referencing the workbook I'm interested in wrong?
Thx,
mv
Ok.
When you open the workbook programmitically do you not store the reference of the workbook and the excel instance that is used to open the workbook. Can you paste the code that is used to open the workbook programmitically.thanks.
$wapnil
When you open the workbook programmitically do you not store the reference of the workbook and the excel instance that is used to open the workbook. Can you paste the code that is used to open the workbook programmitically.thanks.
$wapnil
ASKER
See below. I don't store it. That's another issue (and another open EE question) I have: how to get the file path and name? 'Cause with the below if a user selects a different name or path than the hardcoded, it's obviously not going to work.
Thanks!
mv
stDocName = "rpt_OverviewforExport"
DoCmd.OpenReport stDocName, acPreview
DoCmd.OutputTo acReport, stDocName
Set xlApp = CreateObject("excel.applic ation")
xlApp.Visible = True
xlApp.Workbooks.Open ("C:\TVS\rpt_OverviewforEx port.xls")
xlApp.Application.ActiveWo rkbook.Run AutoMacros (xlAutoOpen)
Call FormatOverviewforExportRpt
DoCmd.Close acReport, stDocName, acSave
Thanks!
mv
stDocName = "rpt_OverviewforExport"
DoCmd.OpenReport stDocName, acPreview
DoCmd.OutputTo acReport, stDocName
Set xlApp = CreateObject("excel.applic
xlApp.Visible = True
xlApp.Workbooks.Open ("C:\TVS\rpt_OverviewforEx
xlApp.Application.ActiveWo
Call FormatOverviewforExportRpt
DoCmd.Close acReport, stDocName, acSave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dim gobjExcel As Excel.Application
Dim WS As Excel.Worksheet
Set gobjExcel = GetObject(, "Excel.Application")
on error resume next
Set WS = gobjExcel.Workbooks("rpt_O
If WS is nothing then
Msgbox "Kindly keep the required sheet open"
Exit Sub/Function
End If
' continue with your error handling
On Error Goto <Errorhandler>
Let me know.
$wapnil