Excel and Access Macros

[akn112]

I've searched everywhere but havent found a clear cut answer. I want to convert my excel macro into a sub that my access macro can use in RunCode
right now i have it as my sub is


Sub RunMacro(GlobalVar)
On Error GoTo Err_RunMacro
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.WorkBooks.Open "C:\Documents and Settings\" & GlobalVar & "\Desktop\FAO Report\FAO90.xls"
With XL.Application
'Copy and Pasted Excel Macro
End With
Set XL = Nothing
Exit_RunMacro:
Exit Sub
Err_RunMacro:
MsgBox Err.Description
Resume
End Sub

It seems to always end at Application.InchestoPoint (within the excel macro). I have also included the library from Excel, through the references tab. Is this a possible task? Attached is a copy of my macro. :help

[Norie]

That's probably because Access doesn't have InchesToPoints and Application refers to the Access application not Excel.

You do have an instance of Excel though, XL and should use that.

[akn112]

Hey Norie. I got it to work simply by deleting the inchestopoint function. Thanks! But now im getting a new error. Every second time i run this code, it gives me runtime error 1004: Method 'Range' of object '_Global' Failed

My slightly modified code includes

XL.quit
XL.workbooks.save
XL.workbooks.close

Because i thought it was a variable problem. But...to my dissappointment, still the same. Anyone seen this problem before?:banghead:

[Norie]

It might help to see all your code.

Especially the line generating the error.:)

[akn112]

the line thats generating the error is


Range("A2").Select

its the first line in the execution of the excel macro.

[Norie]

Could you please post all the code?

What is that line of code meant to do?

Again, Range doesn't exist in Access VBA.:)

If you are automating Excel from Access, or any other application, you need to make sure you reference everything properly..

[akn112]

Hey Norie. theres an attachment on the original post. The problem i don't understand is how come it works every second time. Like id run it once, and it goes through perfectly, and then if i run it again, it throws the error. If i run it once more, its perfect again. And it continues on like that. I tried watching the "immediate window" but with no luck. btw, thanks for your time. i really appreciate it!

[Norie]

Selection and Range do not exist in Access VBA.

[akn112]

I know, i uploaded it again. The first time i uploaded i forgot to include the Excel objects (which you can do manually through the References tab) What this does is it allows you to use Excel objects in an Access environment. The new uploaded file includes that option (which is why the filesize is larger)

[Norie]

Sorry I didn't notice the attachments.:oops:

I've downloaded the first one and again the problem I see is you aren't referencing Excel correctly.

I know by setting a reference under Tools>References... you'll have access to the Excel VBA object model but that doesn't mean you can code as you have.

I've just downloaded your second attachment and again I see the same problem.

For example what does Selection refer to here?


With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
By the way you generally (almost never in fact) need to use Select/Activate.

For example the above code can be replaced with this.


With .Range("A1:T1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


PS I suggest you check Task Manager, you might find a 'ghost' instance of Excel kicking about.:)

[geekgirlau]

Just a couple of points here to clarify:

Once you have created your XL object, all of the code you've copied from your Excel macro needs to be preceded with a full stop. For example,


Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select[/CODE
should become
[CODE].Range(.Selection, .ActiveCell.SpecialCells(xlLastCell)).Select
This indicates that the .Range object relates to XL, and not to Access. You will find a number of times in your code that you have "Selection", "Range", "Columns" etc. that need to be preceeded with a full stop - Access will not recognise these objects unless you tell it that they relate to Excel.
As Norie has already pointed out, it's a good idea to avoid "Select" in your macros. So instead of


.Range("A1:T1").Select
With .Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

you have


With .Range("A1:T1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

It pays to know what Excel sets as a default. For example, with the code snippet above, I would hazard a guess that all you are trying to do is centre the text in the cell, as every other option is set to the default value. If that is the case, all you need is


.Range("A1:T1").HorizontalAlignment = xlCenter

Because you are using "CreateObject" to reference Excel, you may end up with a whole lot of instances of Excel running. Often it's a good idea to try "GetObject" first to see if Excel is already running, and only try "CreateObject" if the first attempt generates an error.

[akn112]

thanks for the helpgeekgirlau and norie! with the feedback from you two, i finally got it to work. What im still not sure about is how come it still works every second time i run it? Anyways, it runs everytime now that i preceeded everything with a dot. Esp. thanks to Norie for helping me all the way from the beginning.

[Norie]

Glad to see you've got everything sorted.:)

I honestly couldn't tell you why the code would only work at particular times.

Perhaps it's something to do with the 'ghost' instance I mentioned.