Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-05-2008, 04:34 AM   #1
dmonney
Registered User
 
Join Date: Dec 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
dmonney is on a distinguished road
excel macro won't run twice

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

dmonney is offline   Reply With Quote
Old 08-05-2008, 05:39 AM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 08-05-2008, 05:49 AM   #3
dmonney
Registered User
 
Join Date: Dec 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
dmonney is on a distinguished road
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

dmonney is offline   Reply With Quote
Old 08-05-2008, 05:50 AM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
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")
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 08-05-2008, 05:56 AM   #5
dmonney
Registered User
 
Join Date: Dec 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
dmonney is on a distinguished road
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
dmonney is offline   Reply With Quote
Old 08-05-2008, 05:58 AM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 05-27-2009, 07:41 AM   #7
Hathman
Registered User
 
Join Date: May 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Hathman is on a distinguished road
Question Re: excel macro won't run twice

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

Hathman is offline   Reply With Quote
Old 05-27-2009, 07:51 AM   #8
SOS
Registered Lunatic
 
SOS's Avatar
 
Join Date: Aug 2008
Location: Somewhere between here and there
Posts: 3,517
Thanks: 2
Thanked 59 Times in 54 Posts
SOS is a glorious beacon of light SOS is a glorious beacon of light SOS is a glorious beacon of light SOS is a glorious beacon of light SOS is a glorious beacon of light
Re: excel macro won't run twice

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
SOS is offline   Reply With Quote
Old 05-27-2009, 08:04 AM   #9
Hathman
Registered User
 
Join Date: May 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Hathman is on a distinguished road
Re: excel macro won't run twice

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
Hathman is offline   Reply With Quote
Old 05-27-2009, 08:07 AM   #10
SOS
Registered Lunatic
 
SOS's Avatar
 
Join Date: Aug 2008
Location: Somewhere between here and there
Posts: 3,517
Thanks: 2
Thanked 59 Times in 54 Posts
SOS is a glorious beacon of light SOS is a glorious beacon of light SOS is a glorious beacon of light SOS is a glorious beacon of light SOS is a glorious beacon of light
Re: excel macro won't run twice

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.

SOS is offline   Reply With Quote
Reply

Tags
excel , macro

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run Macro in VBA help AnnPhil Modules & VBA 3 04-01-2008 07:17 PM
Excel crashes when same report is run again carrie09 Modules & VBA 15 09-23-2007 11:42 PM
Access macros that run excel macros pltnsgt Macros 1 04-11-2007 05:59 AM
Executing MS Excel Macro from MS Access fulltime Forms 0 04-04-2006 02:11 AM
Run Excel Macro from Access chthomas Modules & VBA 0 02-21-2000 12:36 AM




All times are GMT -8. The time now is 09:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World