excel macro won't run twice (1 Viewer)

dmonney · Aug 5, 2008

I have an excel macro that all it does is export a table into excel, turns the field names to 90 degrees, copies the chart as a picture and exits. This macro will run several times in a given session. The problem is that on the second run it dies on "with Rows("1:1") The error is "Method 'Rows' of object '_Global' failed. I thought I reset everything but I guess not. I have also tried making the application not visable. It will run but then i don't get my picture copied.

Code:

Set xlapp = CreateObject("Excel.Application")
            xlapp.Application.Visible = True
            Set xlwkb = xlapp.Workbooks.Open(sfile)
            With xlwkb
            'Rows("1:1").Select
            With Rows("1:1")
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 90
                .AddIndent = False
                .ShrinkToFit = True
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            Cells.EntireColumn.AutoFit
            'Range("A1:AM4").Select
            Range("A1:AM4").CopyPicture
            End With
            xlwkb.Saved = True
            xlwkb.Close
            xlapp.Quit
            Set xlwkb = Nothing
            Set xlapp = Nothing

boblarson · Aug 5, 2008

You are using non explicit referencing which will cause your xlapp to remain open until Access closes, even though you are trying to quit it and set to nothing. Change those references and it should start to work for you.

The ones I am talking about are

With Rows
Range
Cells

Each of those needs to be referenced explicitly with an Excel object:

for example:

With xlwb.Worksheets(1).Rows

xlApp.ActiveSheet.Range("A1:AM4").Select

etc.

Non explicit referencing causes Access to hold the Excel reference open until it closes because it doesn't map back to an actual instance of an object that Access can say - okay he wants to use this. And therefore it creates it's own reference of an object to use.

dmonney · Aug 5, 2008

Still fails

I have also tried a few things trying to get it to close since i posted. it still fails, this is my code now.

Code:

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ztransposed", sfile
            Set xlapp = New Excel.Application
            'Set xlapp = CreateObject("Excel.Application")
            xlapp.Application.Visible = True
            Set xlwkb = xlapp.Workbooks.Open(sfile)
            With xlwkb
            With Worksheets(1).Rows("1:1")
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 90
                .AddIndent = False
                .ShrinkToFit = True
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            Worksheets(1).Cells.EntireColumn.AutoFit
            Worksheets(1).Range("A1:AM4").CopyPicture
            End With
            Excel.Application.UserControl = True
            xlwkb.Saved = True
            xlapp.Workbooks.Close
            xlapp.Application.Quit
            Set xlwkb = Nothing
            Set xlapp = Nothing

boblarson · Aug 5, 2008

This will still fail

With Worksheets(1).Rows("1:1")

You have to explicitly reference an Excel object you've created:

With xlwkb.Worksheets(1).Rows("1:1")

dmonney · Aug 5, 2008

You're a Genius

It worked perfectly (code below)

Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ztransposed", sfile
            'Set xlapp = New Excel.Application
            Set xlapp = CreateObject("Excel.Application")
            xlapp.Application.Visible = True
            Set xlwkb = xlapp.Workbooks.Open(sfile)
            xlwkb.Worksheets(1).Range("A1").ClearContents
            'Rows("1:1").Select
            With xlwkb.Worksheets(1).Rows("1:1")
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 90
                .AddIndent = False
                .ShrinkToFit = True
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            xlwkb.Worksheets(1).Cells.EntireColumn.AutoFit
            'Range("A1:AM4").Select
            xlwkb.Worksheets(1).Range("A1:AM4").CopyPicture
            Set paster = pptSlide.Shapes.Paste
            Excel.Application.UserControl = True
            xlwkb.Saved = True
            xlapp.Workbooks.Close
            xlapp.Application.Quit
            Set xlwkb = Nothing
            Set xlapp = Nothing

boblarson · Aug 5, 2008

Nope, not a genius, just one who has been bitten by this issue before personally and therefore knew about it.

Glad it is now working for you.

Hathman · May 27, 2009

I have a similar problem (I think) that has puzzled me for way too long now and I would be ever grateful if someone can point out my folly here. I can run the code below, once. Upon running it a second time within the same access session it crashes telling me that an "object variable or With block variable not set". This occurs on "Selection.End(xlDown).Select
".
I have tried to use all the correct absolute references to workbooks and worksheets but it still fails every time. The essence of my code here is to export a file from Access, open a template excel file and copy data from the export file to the excel template. Not rocket science but clearly there's something wrong with my code. Closing and restarting Access allows me to run the code successfuly again... but only once.

Dim FilePath As Variant
Dim ex2 As Excel.Application
Dim wbkData2 As Workbook
Dim wbkPPU2 As Workbook
Dim lastrow As Long
Dim wksData2 As Worksheet
Dim wksPPU2 As Worksheet
'OPEN SAVE FILE DIALOG AND GET FULL PATH (ExportFile is separate module)
FilePath = ExportFile
'EXPORT FILE
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PROJECT_PLAN_UPDATES_EXPORT", FilePath
'OPEN FILE AND COPY SELECTED ROWS
Set ex2 = Nothing
Set ex2 = New Excel.Application
With ex2
.Visible = True
Set wbkData2 = Nothing
Set wbkData2 = .Workbooks.Open(FilePath)
With wbkData2
Set wksData2 = .Worksheets(1)
wksData2.Activate
With wbkData2.ActiveSheet.Range("A2").Select
Selection.End(xlDown).Select '<--- CRASHES HERE ON SECOND RUN
lastrow = ActiveCell.Row
wbkData2.ActiveSheet.Range("A2:S" & lastrow).Select
wbkData2.ActiveSheet.Range("A2:S" & lastrow).Copy
End With
End With
End With

'OPEN TEMPLATE FILE AND PASTE VALUES FROM FIRST FILE
With ex2
.Visible = True
Set wbkPPU2 = Nothing
Set wbkPPU2 = .Workbooks.Open("C:\project\PPU.xls")

With wbkPPU2
Set wksPPU2 = .Worksheets(1)
With wksPPU2
.Range("A10").Select
With Selection
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
.Range("J6").Select
With Selection
.FormulaR1C1 = Forms!MAIN!txtPeriodStart
End With
.Range("J6").Copy
End With

End With

End With

wbkData2.Close False
Set wbkData2 = Nothing
Set wbkPPU2 = Nothing
ex2.Quit

SOS · May 27, 2009

You have non explicit references in here also. You need to change this:

Selection.End(xlDown).Select

to this

ex2.Selection.End(xlDown).Select

And this:
lastrow = ActiveCell.Row
to
lastrow = wbkData2.ActiveCell.Row

And this:
With Selection

to this
With ex2.Selection

Hathman · May 27, 2009

Success!!! You are my hero!

I had not tried "ex2." in front of "selection" and that was the fix!

Just as an fyi, it did not like wbkdata2.activecell.row. Instead, I tried ex2.activecell.row and that worked beautifully!

I am forever in your debt. Where do i send money?

Thanks,
Hathman

SOS · May 27, 2009

Glad it worked out for you. The key is to remember to ALWAYS use instantiated objects to refer to things when using Excel Automation. Otherwise Access will just create a new instance "behind the scenes" and it will stay open until Access closes or you terminate from the Task Manager.

excel macro won't run twice (1 Viewer)

dmonney

Registered User.

boblarson

Smeghead

dmonney

Registered User.

boblarson

Smeghead

dmonney

Registered User.

boblarson

Smeghead

Hathman

New member

SOS

Registered Lunatic

Hathman

New member

SOS

Registered Lunatic

Similar threads

Users who are viewing this thread