problem using VBA to open excel and run macro

[DaMorf]

Hi,

i have a button in access that runs a macro to ouput to excel, i have also written into the same button code to open a second excel file.

The second excel file has code written into it to run a macro on open and is linked to the download file.

The problem i have is that when the macro finishes in the second excel file the database code causes the error message "Subscript out of range"

the code in the button is as follows :

Public Sub ExcelD1_Click()
On Error GoTo Err_ExcelD1_Click

Dim stdocname As String

stdocname = "ExcelExp1"
DoCmd.RunMacro stdocname



Dim path As String

Dim a As Excel.Application

Dim b As Excel.Workbook

Dim c As Excel.Worksheet



path = "\\t001dfsd3.axa-uk.intraxa\shared3\union_a_sales\planning\Team\(7) Andrew Morfey\Payroll DB Excel Download Files\"


path = path + "ExpWholeFormatted.xls"


Set a = CreateObject("Excel.Application")


Set b = GetObject(path)


Set c = b.Worksheets(Sheet1)


Exit_ExcelD1_Click:
Exit Sub


Err_ExcelD1_Click:
MsgBox Err.Description
Resume Exit_ExcelD1_Click

End Sub

Any help appreciated,

Thanks,

 

[SkipVought]

Hi,


"Subscript out of range" means that you have an object reference in your code that does not exist, like a workbook or worksheet.

It's probably

Set c = b.Worksheets(Sheet1)

if you have not assigned a value to Sheet1

OR did you intend...

Set c = b.Worksheets("Sheet1")

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[DaMorf]

Thanks Skip,

That got rid of the error message but the second excel sheet closes itself once the macro has run,

how do i get it to stay open?

Thanks

 

[SkipVought]

I dunno???

What's the code in the Excel workbook? Does it close due to some statement in the workbook?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

Probably, it terminates when YOUR CODE ends. It seems it is running in the codespace of your Access VBA.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[DaMorf]

Nothing special,

Private Sub workbook_open()

Run "Macro1"

End Sub

but if i just open the excel file it doesn't close, it only does it when i use the Access button.

Cheers,