Excel Automation and variables

[DancesInGarden]

Using Microsoft Office 2003.

From Access, triggered by a button click on a form, I have a piece of code that opens Excel, opens a specific file, activates a specific sheet in that workbook, prints it, then closes both the workbook and Excel.

When I use absolute names, it works wonderfully. However, I want the user to be able to select a sheet name from a listbox, and pass that as a variable for the name. Therein lies the problem.

Using the absolute name:

Set Sheet = MyXL.ActiveWorkbook.Worksheets("Sheet1")
 
Sheet.Activate
MyXL.ActiveSheet.PrintOut

I can get the variable:

SheetName = [WorksheetList].[value]

However, I can't seem to refer to the variable properly in place of ("Sheet1"). It has something to do with the quotes, I am sure. They are required. I have had no luck "building" quotes onto the SheetName using & and such.

Any ideas? I am desperate and ready to go out and lie on the lawn in hopes seagulls will peck me to death and I won't have to look at this any more.

[MikeTheBike]

Using Microsoft Office 2003.

From Access, triggered by a button click on a form, I have a piece of code that opens Excel, opens a specific file, activates a specific sheet in that workbook, prints it, then closes both the workbook and Excel.

When I use absolute names, it works wonderfully. However, I want the user to be able to select a sheet name from a listbox, and pass that as a variable for the name. Therein lies the problem.

Using the absolute name:

Set Sheet = MyXL.ActiveWorkbook.Worksheets("Sheet1")
 
Sheet.Activate
MyXL.ActiveSheet.PrintOut

I can get the variable:

SheetName = [WorksheetList].[value]

However, I can't seem to refer to the variable properly in place of ("Sheet1"). It has something to do with the quotes, I am sure. They are required. I have had no luck "building" quotes onto the SheetName using & and such.

Any ideas? I am desperate and ready to go out and lie on the lawn in hopes seagulls will peck me to death and I won't have to look at this any more.

Hi

I'm just a little confused, alittle more info on how you envusage this running.

What here is ths snippet of code placed, and what do tme element reresent.

SheetName = [WorksheetList].[value]

??
I feel sure this is possible but doon't know your precise requirements, ie where and what is "list"

this may help ?

Dim sht As Worksheet
For Each sht In MyXL.ActiveWorkbook.Sheets
    MsgBox sht.Name
Next sht

This perhaps could be used to load a list to select from ??

MTB

[ADezii]

Using Microsoft Office 2003.

From Access, triggered by a button click on a form, I have a piece of code that opens Excel, opens a specific file, activates a specific sheet in that workbook, prints it, then closes both the workbook and Excel.

When I use absolute names, it works wonderfully. However, I want the user to be able to select a sheet name from a listbox, and pass that as a variable for the name. Therein lies the problem.

Using the absolute name:

Set Sheet = MyXL.ActiveWorkbook.Worksheets("Sheet1")
 
Sheet.Activate
MyXL.ActiveSheet.PrintOut

I can get the variable:

SheetName = [WorksheetList].[value]

However, I can't seem to refer to the variable properly in place of ("Sheet1"). It has something to do with the quotes, I am sure. They are required. I have had no luck "building" quotes onto the SheetName using & and such.

Any ideas? I am desperate and ready to go out and lie on the lawn in hopes seagulls will peck me to death and I won't have to look at this any more.

Try:

SheetName = Me![WorksheetList]
Set Sheet = MyXL.ActiveWorkbook.Worksheets(SheetName)

[DancesInGarden]

SheetName = Me![WorksheetList]
Set Sheet = MyXL.ActiveWorkbook.Worksheets(SheetName)

--------------------------------------------------------------------------------
Okay, I had tried that and it did not work for me. Then I went back and checked, and it seemed even with the absolute names it was not activating the proper worksheet, although I swear at some time it was. This is what I have:

Sub GetExcel()
Dim MyXL As Object
Dim ExcelWasNotRunning As Boolean
Dim Sheet As Object
 
On Error Resume Next
 
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
 
Set MyXL = GetObject("c:\Test.XLS")
 
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
 
 
Set Sheet = MyXL.ActiveWorkbook.Worksheets("Test2")
Sheet.Activate
Sheet.PrintOut
 
MyXL.Close False
MyXL.Quit
 
Set MyXL = Nothing
Set Sheet = Nothing
End Sub
 

I copied the above from various sources around the internet to play with and come up with something that works.

Basically there is an Excel workbook that contains several worksheets. Each named sheet is a blank template. On an access form is a combo box that lists the name of each worksheet. I want the user to be able to choose a worksheet name, then click a button to trigger Excel to start, open the file to that specific worksheet, print it out, then close the file and Excel.

I need to pass the worksheet name as a variable instead of using, say, cases because from time to time worksheets will be added to this workbook (and of course added to the value list of the combobox). I don't want anybody to have to adjust the code when that happens.

I hope that is clearer!

[DancesInGarden]

MikeTheBike - that would be great if I can populate the list that way. Only I have no idea where to put that code! It would have to be separate from the click event, because the list needs to be populated before that is even triggered.

Would it go into an event for the form itself, or for the drop down/list it is to populate?

As for the original post, I might be able to get around it by saving each worksheet as it's own named workbook, and listing that file path and name in a table, using that to populate the list. That seems to work and I can pass the variable fine without the quotes. So it seems my REAL issue is not being able to activate the proper worksheet. If I can lick that, I think I might be in the clear and NOT have to create a million new files.

I appreciate any help at all!

[MikeTheBike]

MikeTheBike - that would be great if I can populate the list that way. Only I have no idea where to put that code! It would have to be separate from the click event, because the list needs to be populated before that is even triggered.

Would it go into an event for the form itself, or for the drop down/list it is to populate?

As for the original post, I might be able to get around it by saving each worksheet as it's own named workbook, and listing that file path and name in a table, using that to populate the list. That seems to work and I can pass the variable fine without the quotes. So it seems my REAL issue is not being able to activate the proper worksheet. If I can lick that, I think I might be in the clear and NOT have to create a million new files.

I appreciate any help at all!

Hi

You could try something like this in the forms Load event (assuming the combo control is named WorksheetList).

Private Sub Form_Load()
On Error GoTo ErrorHandler
    Dim XLOpen As Boolean
    Dim MyXL As Excel.Application
    Dim sht As Excel.Worksheet
    Dim ValueList As String
 
    XLOpen = False
    Set MyXL = CreateObject("Excel.application")
    XLOpen = True
 
    MyXL.Workbooks.Open CurrentProject.Path & "\SheetOffset.xls"
 
    For Each sht In MyXL.Worksheets
        ValueList = ValueList & ";" & sht.Name
    Next sht
 
    ValueList = Mid(ValueList, 2)
 
    WorksheetList.RowSource = ValueList
 
    MyXL.Quit
 
    Set MyXL = Nothing
    Exit Sub
 
ErrorHandler:
    If XLOpen Then MyXL.Visible = True
    MsgBox Err.Description
End Sub

Note, the error handling is very rudermentary, just enough to let me prove the principle (without multiple instances of excel I can't see!).

After this you can select the required sheet and open Excel and the workbook again as necessary !!??

MTB

ps. the combo's Row Source Type should be set to Value List

[ADezii]

MikeTheBike - that would be great if I can populate the list that way. Only I have no idea where to put that code! It would have to be separate from the click event, because the list needs to be populated before that is even triggered.

Would it go into an event for the form itself, or for the drop down/list it is to populate?

As for the original post, I might be able to get around it by saving each worksheet as it's own named workbook, and listing that file path and name in a table, using that to populate the list. That seems to work and I can pass the variable fine without the quotes. So it seems my REAL issue is not being able to activate the proper worksheet. If I can lick that, I think I might be in the clear and NOT have to create a million new files.

I appreciate any help at all!

This different approach has been tested and does work. It reads the Sheet Name from a List Box, opens the appropriate Excel Workbook, then activates the proper Worksheet. The rest should be a piece of cake:

Dim ExcelApp As New Excel.Application
Dim ExcelFile As New Excel.Workbook
Dim ExcelSheet As New Excel.Worksheet
Dim SheetName As String
 
If IsNull(Me![WorksheetList]) Then Exit Sub
SheetName = Me![WorksheetList]
 
 
 
ExcelApp.Visible = True
Set ExcelFile = ExcelApp.Workbooks.Open("C:\Test\Test.xls")
Set ExcelSheet = ExcelFile.Worksheets(SheetName)
 
ExcelSheet.Activate
  'not sure what you have in mind here
 
ExcelFile.Close True
ExcelApp.Quit
 
Set ExcelSheet = Nothing
Set ExcelFile = Nothing
Set ExcelApp = Nothing