Help with Runtime error 1004: Method 'Rows' of object '_Global' failed

[dpfaff]

Hi,

I have a situation where I am encountering the runtime error 1004 only on every OTHER time I run my macro.

Quick background: I'm making a report for work. They want the report in a tabbed-spreadsheet format. I have Access functions with VBA code to run and export the query to Excel, move "Issue Types" to their own dedicated tab, and perform a lot of formatting, close Excel and sent the report as an email attachment. Which all works fine, however it is contingent upon this next part executing successfully

The code below is the one piece that I cannot resolve. It errors at the line I've bolded in red when I attempt to run it a 2nd time (the report needs to be sent multiple times per day). On the 3rd time it works again, not on the 4th, and so on. I'm testing this on the same file, so there is no difference in the file between the multiple attempts.

Function TEST()

Dim XLAPP As Excel.Application
Set XLAPP = CreateObject("Excel.Application")
XLAPP.Visible = True
'opens exported query (Excel)
XLAPP.Workbooks.Open "M:\New Business - WC\New Business\WC_New_Case_Tracking\RR_Exceptions_Report.xlsx", True, False

Set XLAPP = Nothing
'inserts blank lines between different "Type" values in column A
        Dim LR As Long, i As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        For i = LR To 3 Step -1
            If Cells(i, "A") <> Cells(i - 1, "A") Then Rows(i).Insert xlShiftDown
        Next I

End Function

Column A in my spreadsheet is for "Issue Type", of which there are 5. The code is supposed to insert a blank line in between rows when it sees a change in the "Issue Type".

Again this is working every other time I run it. First attempt executes flawlessly, but the second attempt gives the error UNLESS I close out of the database and go back in. I know this error can occur when objects aren't called out specifically... but I'm really thrown off by it working one time, but not the next.

Any ideas? Thanks!

[ssanfu]

Why are you setting XLAPP to nothing before you try and modify the rows?

Here is an partial example of my code to add two blank lines (rows1 & 2), then enter data into cells A1 & A2.
I use xlx instead of XLAPP... note when i set xlx to nothing.
also look at how I reference the line to insert the blank rows..

   Dim xlx As Object, xlw As Object, xls As Object, xlc As Object

   Dim blnEXCEL As Boolean

   blnEXCEL = False

   ' Establish an EXCEL application object
   On Error Resume Next
   Set xlx = GetObject(, "Excel.Application")
   If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
   End If
   Err.Clear
   On Error GoTo 0

   ' Change True to False if you do not want the workbook to be
   ' visible when the code is running
   xlx.Visible = False

   ' Replace C:\Filename.xls with the actual path and filename
   ' of the EXCEL file into which you will write the data
   Set xlw = xlx.Workbooks.Open(pWkshtPathName)

   'get worksheet name

   ' Replace WorksheetName with the actual name of the worksheet
   ' in the EXCEL file
   ' (note that the worksheet must already be in the EXCEL file)
   Set xls = xlw.Worksheets(1)

   ' Replace A1 with the cell reference into which the first data value
   ' is to be written
   Set xlc = xls.Range("A1")   ' this is the first cell into which data go

   xls.Rows("1").EntireRow.Insert
   xls.Rows("1").EntireRow.Insert
   xls.Range("A1").Select
   xls.Range("A1").FormulaR1C1 = pVendor
   xls.Range("A2").Select
   xls.Range("A2").FormulaR1C1 = pMthYr

   ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
   Set xlc = Nothing
   Set xls = Nothing
   xlw.Close True   ' close the EXCEL file and save the new data
   DoEvents
   Set xlw = Nothing
   If blnEXCEL = True Then
      xlx.Quit
   End If
   Set xlx = Nothing

[dpfaff]

Thanks Steve. I removed the errant "Set XLAPP = Nothing"

I found a thread that explains why I'm having the issue every other time it runs. So that, along with your code, I'm trying different things out. The main problem I'm having is I don't understand VBA well enough to adapt a proper reference into what I'm trying to have the code do.

http://www.mrexcel.com/forum/microso...lications.html

[ssanfu]

Yes, using Access to control Excel (Automation) is difficult t times (at least for me - I rarely use automation ). So, in my code, I don't use the "WITH" construct, but fully qualify the steps. That way I know what object I am referring to in the code.

In my code:
xlx is the Excel application
xlw is the workbook
xls is the worksheet
xlc is a cell (apparently, looking at the code, I didn't use )


Good luck with your project.