Run-Time 1004 Error When Running Excel VBA Code In Access

[J3nny]

Hi,

I have a Sub in Access that runs a macro in Excel, I had a bit of trouble getting it working and now despite working 90% of the time every so often it crashes my database at the point of running the Excel macro, so I'm trying to get the excel macro to run in Access to save it trying to jump into Excel.

Someone suggested to just copy and paste the code from Excel into Access which is what I did and I swear this worked a few times, but I made a couple of changes and it started giving me compile errors at the Application.Calculation line. I put objExcel. in front of it and now it seems to be ok with that, but it gets stuck with a run-time 1004 error at the ActiveWorkbook.SaveAs line and the error text says 'SaveAs method of Workbook class failed'. Could someone please help me? As you can tell VBA programming is really not my forte..

Public Sub ExcelManipulation()

Dim objExcel As Object
Dim objWorkBook As Object
'New Email Code
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
'New Email Code

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("H:\IT Department\General\Reporting\Season Ticket Analysis\Season Ticket Analysis - Data.xlsm")
objExcel.Visible = True
'objExcel.DisplayAlerts = False

'TWait = Time
'TWait = DateAdd("s", 5, TWait)
'Do Until TNow >= TWait
'     TNow = Time
'Loop

    ChDir "H:\IT Department\General\Reporting\Season Ticket Analysis"
    objExcel.Workbooks.Open FileName:= _
        "H:\IT Department\General\Reporting\Season Ticket Analysis\Season Ticket Analysis - MergeDoc.XLSX" _
        , UpdateLinks:=3
    
    objExcel.Calculation = xlManual
    
    ChDir _
        "H:\IT Department\General\Reporting\Season Ticket Analysis\Report Archive"
 [COLOR=Yellow]   [COLOR=Red]ActiveWorkbook.SaveAs FileName:= _
        "H:\IT Department\General\Reporting\Season Ticket Analysis\Report Archive\Season Ticket Analysis " & Format(Date, "yymmdd") & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False[/COLOR][/COLOR]
        
    'objExcel.Calculation = xlAutomatic

    
    ActiveWorkbook.Sheets("To Target").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    objExcel.CutCopyMode = False
    ActiveSheet.Range("A1").Select
    
    ActiveWorkbook.Sheets("Season Comparison").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    objExcel.CutCopyMode = False
    ActiveSheet.Range("A1").Select
    
    ActiveWorkbook.Sheets("Cumulative Figures").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    objExcel.CutCopyMode = False
    ActiveSheet.Range("A1").Select
    
    ActiveWorkbook.Sheets("Cancellations").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    objExcel.CutCopyMode = False
    ActiveSheet.Range("A1").Select
    
    ActiveWorkbook.Sheets("Summary Figures").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    objExcel.CutCopyMode = False
    ActiveSheet.Range("A1").Select
    
    objExcel.Calculation = xlAutomatic
    
'New Email Code

Email_Subject = "Updated Season Ticket Analysis - " & Format(Date, "dd/mm/yy")
Email_Send_From = "jennyburnie@club.co.uk"
Email_Send_To = "jennyburnie@club.co.uk"
Email_Body = "Hi," & vbNewLine & vbNewLine & _
"Season ticket reports are attached and updated for sales to COB yesterday." 
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.Attachments.Add ActiveWorkbook.FullName
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description

'New Email Code

    objExcel.Windows("Season Ticket Analysis - Data.xlsm").Activate
    objExcel.ActiveWindow.Close

End Sub

 

[pbaldy]

I think you'll want to use your Excel objects for all the Excel related code.

 

[Solo712]

Hi,

I have a Sub in Access that runs a macro in Excel, I had a bit of trouble getting it working and now despite working 90% of the time every so often it crashes my database at the point of running the Excel macro, so I'm trying to get the excel macro to run in Access to save it trying to jump into Excel.

Someone suggested to just copy and paste the code from Excel into Access which is what I did and I swear this worked a few times, but I made a couple of changes and it started giving me compile errors at the Application.Calculation line. I put objExcel. in front of it and now it seems to be ok with that, but it gets stuck with a run-time 1004 error at the ActiveWorkbook.SaveAs line and the error text says 'SaveAs method of Workbook class failed'. Could someone please help me? As you can tell VBA programming is really not my forte..

Public Sub ExcelManipulation()
 
Dim objExcel As Object
Dim objWorkBook As Object
'New Email Code
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
'New Email Code
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("H:\IT Department\General\Reporting\Season Ticket Analysis\Season Ticket Analysis - Data.xlsm")
objExcel.Visible = True
'objExcel.DisplayAlerts = False
 
'TWait = Time
'TWait = DateAdd("s", 5, TWait)
'Do Until TNow >= TWait
'     TNow = Time
'Loop
 
    ChDir "H:\IT Department\General\Reporting\Season Ticket Analysis"
    objExcel.Workbooks.Open FileName:= _
        "H:\IT Department\General\Reporting\Season Ticket Analysis\Season Ticket Analysis - MergeDoc.XLSX" _
        , UpdateLinks:=3
 
    objExcel.Calculation = xlManual
 
    ChDir _
        "H:\IT Department\General\Reporting\Season Ticket Analysis\Report Archive"
 [COLOR=yellow]   [COLOR=red]ActiveWorkbook.SaveAs FileName:= _[/COLOR]
[COLOR=red]"H:\IT Department\General\Reporting\Season Ticket Analysis\Report Archive\Season Ticket Analysis " & Format(Date, "yymmdd") & ".xlsx" _[/COLOR]
[/COLOR][COLOR=red]  , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False[/COLOR]
 
    'objExcel.Calculation = xlAutomatic
 End Sub
:
:

Hi,
there are two issues that I see with the code. One, you need to be aware that the "xl..." constants are not available in Access. The FileFormat parameter in the highlighted statement will have to take either an integer directly or the constant will have to be defined in Access.
However, the Error 1004 most probably relates to the "ActiveWorkbook" object in the statement. This is again an Excel object which in most cases
will not be visible to Access. You will need to declare another object in Access for the workbook you are opening with objExcel...i.e.

Dim objSecondWorkBook As Object

then set it like

Set objSecondWorkBook = objExcel.WorkBooks.Open(...your path and filename )

and finally when saving it, point to it as Access object, ie.

objSecondWorkBook.SaveAs FileName:=.......

Try it to see if it works...

Best,
Jiri