Solved: Run Execl Macro in Access

[kromes]

Ok Here is an my objective:
I am devoloping a data base to link to a spread sheet that is changed daily. This excel file is posted on the common drive. I open the file and run a macro to clean it up and then another macro to save it under another file name. (I could probly do all this in one macro but i have not attempted yet) Any how What I want to do is Add a command button on to the switch board that will open the file and run the macros and close the file.

Can any one help?

[mdmackillop]

Hi Kromes,
This area is not my strong one, but I'll try anyway.
With my limited ability, I would create the macros in Excel and call them from Access, something like the following. In my test, TestLink is a macro saved in a standard module
[VBA]
'In Excel module
Public Sub TestLink(MyData As String)
MsgBox MyData
End Sub



'In Access
Sub Test()
Dim mysheet As Object, myfield As Variant, xlApp As Object

' Set object variable equal to the OLE object.
Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.Workbooks.Open("c:\AAA\MacroBook.xls").Sheets(1)

' Set the Visible property of the sheet to True
' Run the macro in Excel
' Save the sheet, and quit Microsoft Excel.
mysheet.Application.Windows("MacroBook.xls").Visible = True
mysheet.Application.Run "macrobook.xls!testlink", "Malcolm"
mysheet.Application.ActiveWorkbook.Saveas "C:\AAA\TestSave.xls"
mysheet.Application.ActiveWorkbook.Close
xlApp.Quit

' Clear the object variable.
Set mysheet = Nothing

End Sub

[/VBA]

[Justinlabenne]

Could you run the Excel macros from the Excel files Workbook_Open procedure? If so you could have the file cleaned, and backed up without even needing to see it happening. This kb entry is what I use.

Open Excel From Access

[Justinlabenne]

Herea a bit of an example of using "run" to run a select macro that reside within the excel file from Access.

[xCav8r]

I used the method suggested by mdmack and Justin quite frequently. For what you want to do, sounds like the easiest/best approach.

[kromes]

I have been working with this code but have had no luck, The file opens OK but it will not call the macro I have the following code in place "personal.xls!Fder_Sch", "Fder_Sch" but each time I run it I receive a message can not find PERSONAL.XLS.
I beleive the first section is calling for the macro location the next section is starting the macro. Is this correct if not could you explain.

Would you explain this: What is the first section of code doing, what is the rest of the code in this line doing?

[kromes]

I could not get the access file to open, the message said unrecongized databsae format. Is this in a later version of access. I have 97.

[Justinlabenne]

Access does have problems with backwards compatibility, and since I have never used Access 97 I can't be sure, but that is probably the reason you cannot open it.

Now let me get things straight:

1)The Access File opens a specific Excel file
2)When the excel file opens, it will run a code that is in your personal.xls workbook
3)That macro is called "Fdr_Sch"
4)You do have a personal.xls workbook that opens when you open Excel

Is this all correct?

[kromes]

1. yes A cmd buttom has the code, I opens the spread sheet 6_20.
2. when excel opens the code in the command button calls the macro "personal.xls!Fder_Sch"
3. What I discovered when I view the macros. it is called personal.xls!Fder_Sch
4. The presonal work book is in the xlstart folder, it does open when excel is opened. The macro is in the personal.xls spread sheet.

I tried writing a macro and saving it in This work book and the process worked, but this spread sheet will change daily - it is exported and saved as the current - 7_5 and so on so the macro will have to be in the personal.xls folder. Unless I can put it in a modul in access and call it out to run after the spread sheet is open. is this possible?

[mdmackillop]

I've been looking at Justin's example.
1. When Excel is opened by the command button, my Personal.xls is not being opened.
2. If Excel is already open, another instance is being opened by the command button. Personal .xls is not available in this instance either, and is not visible in the VBE.

[kromes]

I used the code you sent, Sorry I did not clerify that early in my replys. The Personal.xls is in the the xlstart, which i believe it opens automaticaly whenever excel is open. Is this correct? But what I am getting from your 2 statment is that even if it is open VB does not recinnize it.
Could I add another varibale to open it as well and what would the sequence be to run the macro in 6_20 spread sheet.

[Justinlabenne]

Since it appears that the name of the Excel file to open is changing daily, this will need to be adjusted. You may need a textbox to plug in the files name, or a browse for file method. But sewaht you can get from this, it assumes there is an Excel file called "TestFile2.xls" in the same location as the Access database with this code.

It should open up your personal.xls, testfile2, and then run the fder_sch macro, may not be perfect, because I have no idea what the fder_sch code does, but it's getting close.

[VBA]Sub OpenSpecific_xlFile_Choose()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String

Dim sProc As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = CurrentProject.Path & "\TestFile2.xls"


' Open it
With oXL
.Visible = True
.Workbooks.Open (oXL.StartupPath & "\Personal.xls")
.Workbooks.Open (sFullPath)

' use (application.run to run a procedure within excel)
.Run "Personal.xls!Fder_Sch"
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub[/VBA]

[kromes]

Ok I pasted the code in and an it below is what I have. I get an error message Variable not defined The debuger referances - CurrentProject What is this line doing, and does changing sub from the code Sub OpenSpecific_xlFile_Choose() to what I have affect it.


[VBA]Private Sub macro_Click()

' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String

Dim sProc As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = CurrentProject.Path & "C:\Fder_Sch\6_20.xls"


' Open it
With oXL
.Visible = True
.Workbooks.Open (oXL.StartupPath & "C:\Program Files\Microsoft Office\Office\XLSTART\Personal.xls")
.Workbooks.Open (sFullPath)

' use (application.run to run a procedure within excel)
.Run "Personal.xls!Fder_Sch"
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit



End Sub[/VBA]

[xCav8r]

I see a few things that look problematic.

First, and not really important, but you've declared three variables that you're not using.

[VBA]
Dim oExcel As Object
Dim sFullPath As String
Dim sProc As String
[/VBA]

Second, you have two OnError lines back to back:

[VBA]
On Error GoTo 0
' Full path of excel file to open
On Error GoTo ErrHandle
[/VBA]

Third, your paths don't look like they will work.

[VBA]sFullPath = CurrentProject.Path & "C:\Fder_Sch\6_20.xls"[/VBA]

This appends "C:\Fder_Sch\6_20.xls" to whatever the value of CurrentProject.Path is. Unless that is nothing, this will result in an invalid path. Also, note that if CurrentProject.Path is in Access, this will only work if your workbook is in the same folder.

Similarly...

[VBA]
Workbooks.Open (oXL.StartupPath & "C:\Program Files\Microsoft Office\Office\XLSTART\Personal.xls
[/VBA]

Looks to be redundant and will result in an invalid path error.

Fifth, you need to quit the excel application you opened, or it will continue running. See below.

[VBA] ErrExit:
oXL.quit
Set oXL = Nothing
Exit Sub
[/VBA]

Finally, I don't see what is causing your variable undefined error. What line is it coming from?

PS. Is oXL.UserControl = True and what does it do?

[Justinlabenne]

Try this: I have been attempting to modify my original code to suit your needs.

[VBA]Option Explicit

Private Sub macro_Click()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String



' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:\Fder_Sch\6_20.xls"


' Open it
With oXL
.Visible = True
.Workbooks.Open ("C:\Program Files\Microsoft Office\Office\XLSTART\Personal.xls")
.Workbooks.Open (sFullPath)

' use (application.run to run a procedure within excel)
.Run "Personal.xls!Fder_Sch"
End With


ErrExit:
oXL.Quit 'Keep this if you want excel to close after running the code
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub[/VBA]

[kromes]

Justinlabenne
Execelent - I loaded the code ran a test and it works. I had to make a slight modification but it was on my end. I have a couple of other things I want to try in the code. But I will work on that and will most likely be back.
THANKS!!!!! for your help I owe you one.
and Thanks to all of those that contributed.

[xCav8r]

Kromes, I'm glad that Justin was able to help you out. Would you consider marking this thread solved?

[kromes]

Yes this has been resolved.

What do I need with this thread?

Again Thanks for the Help

[xCav8r]

At the top of this thread, look for a link that says thread tools. One of the options will be to mark it solved.