Access VBA with Excel Macros

  • Thread starter Thread starter Alain
  • Start date Start date
A

Alain

Hi to all,

I am trying to automate a process of updating an excel
worksheet ( macro exist in excel) via Access.My problem
is when I open excel from Access,I do not see any macros
from Excel.
I need to mention that the worksheet is saved from a
currency internet page, .csv files saved in an excel
format, file saved on a regular basis
how can I access my macro in Excel so it can perform the
needed task before I can upload into my Access table,

Here is the code I have
Public Sub TransData()
' sub to automatically transfer data from excel into the
currency table

Dim strmsg As String, strfile As String, strmac As
String, strperso As String

Dim oExcel As New Excel.Application

strmsg = "Are you sure you want to update the currency
exchange rate ?"
' fichier Canada Bank
strfile = "F:\DRS\Projets\Currency\Canada Bank.xls"
' fichier personal.xls
strperso = "C:\Documents and
Settings\Administrator\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.xsl"
' fichier macro
strmac = "PERSONAL.xls!DailyCurrency"

' warning msg
If MsgBox(strmsg, vbYesNo, "Currency Exchange") = vbNo
Then
Exit Sub
Else
' making excel visible
oExcel.Visible = True
With oExcel
' ouverture du fichier
' .Application.Workbooks.Open strperso, , False
.Application.Workbooks.Open strfile, , False
' exécution de la macro
.Run strmac
' closing excel
.Application.Quit
End With

End If

Any help will be appreciated
Many Thanks

Alain
 
Alain,

Your code was basically correct. You just had a couple
typos. See my comments.

Also, you were opening one workbook, but referencing a
macro in a *closed* workbook. I wasn't sure which
workbook contained the macro you were trying to run, so I
made guess.

I opened the workbook using strperso so that it's macro
would be visible. At least, it worked in my own test.

See below.

Public Sub TransData()

' sub to automatically transfer data from Excel into
the currency table

Dim strmsg As String
Dim strfile As String
Dim strmac As String
Dim strperso As String
Dim oExcel As New Excel.Application

strmsg = "Are you sure you want to update the currency
exchange rate?"
' fichier Canada Bank
strfile = "F:\DRS\Projects\Currency\Canada
Bank.xls" '<--Changed "Projets" to "Projects"
' fichier personal.xls
strperso = "C:\Documents and
Settings\Administrator\Application Data\" _
& "Microsoft\Excel\XLSTART\PERSONAL.xls" '<--
Changed ".xsl" to ".xls"

' fichier macro
strmac = "PERSONAL.xls!DailyCurrency"

' warning msg
If MsgBox(strmsg, vbYesNo, "Currency Exchange") = vbNo
Then
Exit Sub
Else
' making excel visible
oExcel.Visible = True
With oExcel
' ouverture du fichier
.Application.Workbooks.Open strperso, ,
False 'UNcommented this line
' .Application.Workbooks.Open strfile, ,
False 'Commented this line
' exécution de la macro
.Run strmac

' closing excel
.Application.Quit
End With
Set oExcel = Nothing 'Added this line
End If

End Sub
 
Back
Top