Access VBA - Run-time error '1004' - Application defined and _Global error

1
votes

I'm strugling with the following (part of) coding..

'Open Excel
   Set xlApp = CreateObject("Excel.Application")
   xlApp.Visible = True

'Open the workbook and worksheet
   With xlApp
      Set xlWB = xlApp.Workbooks.Open(strFilePathSource)
      Set xlWS = xlWB.Worksheets("DataFromDB")
      With xlWS
         .Activate
         'Delete the named ranges
         On Error Resume Next
         .Names("DataRange").Delete
         .Names("DataTable").Delete
         On Error GoTo 0
         'Copy all records from the recordset
         .Range("A2").CopyFromRecordset rst
         'Add fieldnames
         .Range("A1").Select
            For intX = 0 To rst.Fields.Count - 1
               .Cells(1, intX + 1) = rst.Fields(intX).Name
            Next intX
         .Range("A1").CurrentRegion.Select
         .Names.Add Name:="DataRange", RefersTo:=Selection (*1)
         .ListObjects.Add(xlSrcRange, Range("DataRange"), , xlYes).Name = "Table1" (*2)
         .ListObjects("Table1").TableStyle = "TableStyleMedium2"
         .ListObjects("Table1").Name = "DataTable"
      End With

The first time I run the code, everything works without errors but..

When I close the workbook and let excel open and run the code again I get a Run- time error '1004' with sub remark 'Application-defined or Object-defined error. (Code stops then on line (*1)).

When I do not close the workbook and run the code again I get a Run-time error '1004' with sub remark 'Method 'Range' of Object '_Global' Failed. (Code stops then on line (*2)).

I looked around in all types of forums and still couldn' find a solution. That's why I posted it.

Any support/help/tips would be very much appriciated.

1
vbams-accessruntime-error

1 Answers

0
votes

To create your Excel object, try: 

  On Error Resume Next
  Set xlApp = GetObject(, "Excel.Application")
  If xlApp Is Nothing Then
      Set xlApp = CreateObject("Excel.Application")
  End If
  On Error GoTo 0