Call Excel Macro From Access 1

[ooch1]

Hello,

I would like to call a macro that is currenlty located in Excel file called 'T2C_MODEL.xls'.

The macro name is 'IMPORT_and_FLAG_DATA()' i have received some similar advice before in Thread707-944710, but i'm not sure which bits should actually reference the filepath of the spreadsheet.

My current access module is as follows, but this doesn't work at all:

Option Explicit
Dim T2C As New Excel.Application
Dim T2C As Object
Dim sourcefilepath As String


Sub T2C_Flat_Import()
        Set T2C = New Excel.Application
        sourcefilepath = "D:\02-EXCEL_DOCs\T2C_MODEL.xls"
        Set T2C = Workbooks.Open(Filename:=sourcefilepath) '
        T2C.Run ("IMPORT_and_FLAG_DATA")
        T2C.Close
End Sub

I hope someone knows how to do this as this would help out loads??

OOch

 

[TonyJollans]

Hi OOch,

I've had a look at the referenced threads but I haven't studied them - far too many other issues involved, so I'll just address your request here on its own.

You must address an instance of Excel. You might want to also address your workbook opened in that instance of Excel (let's assume you do). For this you need two object variables (incidentally, your code has two declarations of the same object variable and won't compile)

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook

You will need a reference to "Microsoft Excel x.0 Object Library" for this to work.

Declaring objExcel as [red]New[/red] will create an instance of Excel by itself, so there is no need to do anything further on that score. I wouldn't recommend doing it that way but it's what you have so we'll leave it for now.

With your objects declared and an instance of Excel, you want to open your workbook ..

sourcefilepath = "D:\02-EXCEL_DOCs\T2C_MODEL.xls"
Set [red]objWorkbook[/red] = [red]objExcel.[/red]Workbooks.Open(Filename:=sourcefilepath)

Now you have your workbook open, you can run your macro - the Run method belongs to the Application, not the Workbook, so ..

objExcel.Run ("IMPORT_and_FLAG_DATA")

Finally you want to close your workbook and quit Excel. Putting it all together you get:

[blue]Sub T2C_Flat_Import()

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook

sourcefilepath = "D:\02-EXCEL_DOCs\T2C_MODEL.xls"
Set objWorkbook = objExcel.Workbooks.Open(Filename:=sourcefilepath)

objExcel.Run ("IMPORT_and_FLAG_DATA")

objWorkbook.Close
objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing [green]' No real point in  this with New[/green]

End Sub[/blue]

Of course I don't know what your sub in Excel is doing or whether you want to pass back any results, so it may need to be more complex - post back if that's the case.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[ooch1]

Tony,

I hope you are still around to respond to this (if not then i hope someone else can help me), I have only just got round to trying your code and i'm having problems with the first line.
When i try to run the code it gives me a compile error message stating 'User Defined Type Not Defined'.
I thought this was to do with the references, but i checked them and the items that are selected (by default i suppose?) in the order they appear are:

Visual Basic for Apllications
Microsoft Object 9.0 Object Library
Microsoft ActiveX Data Objects 2.6 Library
Ole Automation

If you can offer some further assistance on this matter then it would be greatfully received.

OOch

 

[TonyJollans]

Hi OOch,

You need to set a reference to the Excel Library as well. It is called something like [blue]Microsoft Excel 9.0 Object Library[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[ooch1]

Tony,

Sorry to keep pestering you, but i have checked this in Excel and it is definetly flagged, sorry i have know idea why this is still being causing problems??

OOch

 

[xlbo]

What version of excel are you running ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ooch1]

Geoff,

I'm currently running Excel 2000.

To be honest i'm having real problems with all this. I have since tried opening the file as an object which seems to work ok, but i cannot remove any warnings as it doesn't recognise 'setwarnings = false' or 'displayalerts = false'.

The current problem is that it won't recognise the csv file format and I get the error saying that the varaible is not defined, which also happens with the above 2 commands.

The latest code is as follows:

Function T2C_Flag_Import()
Dim Conn As Connection
Dim objXL As Object
Dim objWorkbook As Object
Dim sourcefilepath As String

Set objXL = CreateObject("Excel.Application")
    objXL.Application.Visible = True

sourcefilepath = "D:\02-EXCEL_DOCs\T2C_MODEL.xls"
Set objWorkbook = objXL.Application.Workbooks.Open(Filename:=sourcefilepath)

objXL.Application.Run ("IMPORT_and_FLAG_DATA")

objWorkbook.SaveAs Filename:= _
        "D:\02-EXCEL_DOCs\T2C_MODEL.csv", FileFormat:=[u][b][COLOR=red]xlCSV[/color red][/b][/u], _
        CreateBackup:=False

objWorkbook.Close
objXL.Quit

Set objWorkbook = Nothing
Set objXL = Nothing '

End Function

As i say i'm starting to pull my hair out with all this.

OOch

 

[xlbo]

which warnings ?? If you are talking about the enable macros warning then it cannot be turned off - you would need to set security to low

SetWarnings doesn't exist in excel - you would need to use DisplayAlerts which is a member of the APPLICATION class rather than workbook so it would be:

Application.displayalerts = false

In terms of the file format, I am very surprised at that - did you pick from a list or just type it in ??

Have a look at the saveas syntax in help and see if the CSV file format is xlCSV (it is in XP but maybe not in 2000)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ooch1]

Geoff,

Have just tried that, i then stepped into the code and immediatley got the compile error "method or Data memeber not found".

Does the fact that i'm running this from access make a difference?

Function T2C_Flag_Import()
Dim Conn As Connection
Dim objXL As Object
Dim objWorkbook As Object
Dim sourcefilepath As String

Set objXL = CreateObject("Excel.Application")
    objXL.Application.Visible = True

sourcefilepath = "D:\02-EXCEL_DOCs\T2C_MODEL.xls"
Set objWorkbook = objXL.Application.Workbooks.Open(Filename:=sourcefilepath)

[b][COLOR=blue]Application.displayalerts = False[/color blue][/b]
objXL.Application.Run ("IMPORT_and_FLAG_DATA")

objWorkbook.SaveAs Filename:= _
        "D:\02-EXCEL_DOCs\T2C_MODEL.csv", FileFormat:=xlCSV, _
        CreateBackup:=False

objWorkbook.Close
objXL.Quit

Set objWorkbook = Nothing
Set objXL = Nothing '

End Function

I have also checked the help and the csv file format code is xlCSV. Thinking about this some more could i re-Set the object format once the excel macro has run?

OOch

 

[xlbo]

My bad - should've been more explicit - you need to refer to the EXCEL application object:

objXL.displayalerts = false

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ooch1]

Geoff,

That's solved that problem...good work!

That still leaves me with the save as issue, are there any other workarounds that you can think of?

OOch

 

[xlbo]

What error do you get if you step through the code ??

What does the Import & Flag data macro do (if that isn't a stupid question ;-) ) in relation to the processes it carries out ??

What data is imported...to what and how ?

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ooch1]

Geoff,

As soon as i try to step into the code i get the compile error "Varaible not Defined" and i can't step through the code at all.

The Import and Flag Data macro, resides within the excel file and essentially imports 3 csv files into the excel file, sorts them and suns a check and flags the rows that fit a certain criteria.
This macro works fine when i manually run it and save it as a csv in excel. I then import the saved file into access via and auto exec macro.
Rather than re-write the whole code from within access i just wanted to open, run the macro and save the csv from access.

I feel as though it is almost there, apart from the final save as bit. When i take out this line the rest now works fine, but obviously nothing is being saved to import, which is the whole object of the macro.

OOch

 

[ooch1]

Geoff,

I have a further update which may, or may not, help matters.
I removed the FileFormat:=xlCSV element, to see if it would still save the file......It didn't obviously because that would have been to helpful, but i got a different error message:

Run Time Error 438
Object doesn't support this property or method

Therefore i think the question is how do i save an object as excel...is this worth starting a new thread?

OOch

 

[xlbo]

Hmmm - I think the issue may be in the macro you are trying to run and the final state it leaves the workbook in. Just tried a paired down version of youir code and it works fine (in XL / Access XP)

Other thing you may want to try is changing:

Set objWorkbook = objXL.Application.Workbooks.Open(Filename:=sourcefilepath)

to

objXL.Application.Workbooks.Open(Filename:=sourcefilepath)
Set objWorkbook = objXL.activeworkbook

Which should get rid of any possibility that the object doesn't rrecognise the saveas function (ocaisionally it may if you reference the file via the open method)


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ooch1]

Geoff - I really, really appreciate your patience with all this, but i'm still not having any luck.

I am now getting a syntax error message on the first line.

[COLOR=red]
objXL.Application.Workbooks.Open(Filename:=sourcefilepath)[/color red]
Set objWorkbook = objXL.activeworkbook

OOch

 

[xlbo]

good grief - you can tell it's a friday - my mind is obviously already in the pub !

SET objXL.Application.Workbooks.Open(Filename:=sourcefilepath)

Still need the SET keword

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ooch1]

Geoff,

I have good news and bad news. The bad news is that it still didn't work and i got the same syntax arroe message.

I then tried this:

Set objXL.activeworkbook =
objXL.Application.Workbooks.Open(Filename:=sourcefilepath)
Set objWorkbook = objXL.activeworkbook

The strange thing then was that it opened, but still got an error message.
The good news is i managed to sort the problem out by just building the close and save as csv into the excel macro.

I'm not to sure if you have any further ideas, but at least i have a work around now.

Thank you once again for all the time and effort you have given!!!

OOch

 

[PHV]

And what about this ?
objXL.Application.Workbooks.Open Filename:=sourcefilepath
Set objWorkbook = objXL.activeworkbook
Or this ?
Set objWorkbook = objXL.Application.Workbooks.Open(Filename:=sourcefilepath)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244