Define Worksheet Path (1 Viewer)

[KirkComer]

I export data on a daily basis from Access 2007 to Excel 2007 with no problems.

Okay... this works and places the data in the first worksheet of the document.
MySheetPath = "T:\Dept\HotDogs.xlsx"

I have a application where I need to place the data in the second sheet of the document. I tried:
MySheetPath = "T:\Dept\HotDogs.xlsx\Sheet2"
It didn't work.

Any suggestions. I can paste all the code here. I was just hoping this was a quick and easy answer for someone.

 

[KirkComer]

Actually to make things easier to understand... here is the code I started with (the code works great). I'm not ashamed to say it came from Access2007 VBA Programming For Dummies. lol I just need to select the second sheet in the file. This code always selects the first sheet.

Private Sub ExcelExportExample()

'Declare a variable named MySheetPath as String.
Dim MySheetPath As String

'Note: You must change the path and filename below
'to an actual Excel .xlsx file on your own computer.
MySheetPath = "C:\MySheet.xlsx"

'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet.
Set XlSheet = XlBook.Worksheets(1)

'Copy GrandTotal to FromAccess cell in the sheet.
XlSheet.Range("FromAccess").Locked = False
XlSheet.Range("FromAccess") = Me!GrandTotal

'Boldface the new value (optional).
XlSheet.Range("FromAccess").Font.Bold = True

'Save the sheet with the new value (optional).
XlBook.Save

'Close the Access form (optional).
DoCmd.Close acForm, "OrderSummaryForm", acSaveNo

'Clean up and end with worksheet visible on the screen.
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

 

[boblarson]

Change your code with the things in red:

Private Sub ExcelExportExample([B][COLOR=red]MySheetPath As String, WorksheetName As String[/COLOR][/B])

'Declare a variable named MySheetPath as String.
Dim MySheetPath As String

[B][COLOR=red]' No need for the three lines below in green[/COLOR][/B]
[B][COLOR=green]'Note: You must change the path and filename below
'to an actual Excel .xlsx file on your own computer.
'MySheetPath = "C:\MySheet.xlsx"
[/COLOR][/B]
'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
[B][COLOR=red]Set XlBook = Xl.Workbooks.Open(MySheetPath)[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B]
'Make sure everything is visible on the screen.
Xl.Visible = True
 
[COLOR=red][B]' This line below, in green, you do not want
[/B][/COLOR][COLOR=green][B]'XlBook.Windows(1).Visible = True
[/B][/COLOR]
[B][COLOR=red]' Change this to select the sheet you wanted by using the variable and [/COLOR][/B]
[B][COLOR=#ff0000]' get rid of the line in green below[/COLOR][/B]
[COLOR=green][B]'Define the topmost sheet in the Workbook as XLSheet.
[/B][/COLOR]Set XlSheet = XlBook.Worksheets([B][COLOR=red]WorksheetName[/COLOR][/B])

'Copy GrandTotal to FromAccess cell in the sheet.
XlSheet.Range("FromAccess").Locked = False
XlSheet.Range("FromAccess") = Me!GrandTotal

'Boldface the new value (optional).
XlSheet.Range("FromAccess").Font.Bold = True

'Save the sheet with the new value (optional).
XlBook.Save

'Close the Access form (optional).
DoCmd.Close acForm, "OrderSummaryForm", acSaveNo

'Clean up and end with worksheet visible on the screen.
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

The code that you got was in error when it used GetObject because it had a disconnect between the variable, Xl and the XlBook variable. You use the Xl.Workbooks.Open method instead.

And you would call this revised procedure by using:

Call ExcelExportExample("C:\MySheet.xlsx", "Sheet2NameHere"

 

[KirkComer]

Thanks Bob. I will try out your suggestions and report back with how it goes. I've already modified my code a little compared to the original code from the book. I am sure I can apply your instructions as well. I will be back in touch.