Moving Exchange VB Code to MS Access

[nedstar1]

Hi Friends,

I'm working on a way to automatically update Exchange Rate info for a DB I'm working on. In Excel, I found a nifty feature that allows me to import a table from the web. I am using the Federal Reserve Bank in NY as my reference.

Here's the code that Excel generated:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;

http://www.ny.frb.org/markets/fxrates/noon.cfm",

Destination:=Range("A1" _
))
.Name = "noon"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "19"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

This works beautifully in getting the table of data I want into Excel. I then tried to get it to run automatically with no security warnings or prompting, but I was ultimately stymied.

Is there a reasonably easy way to move this code into Access and have it still work? I'm enough of an Access neophyte that I believe this is possible, but I'm not sure.

Can anyone give me a tip or a link or anything otherwise helpful?

TIA

Regards,
Nedstar1

 

[AccessGuruCarl]

Hi Nedstar1,

Try something like this,

Add a reference to Excel

Then add something like this:

Dim msExcel As Excel.Application
Dim strSourceFile as String
Set msExcel = GetObject(Class:="Excel.Application")
strSourceFile = "C:\Dev\test.xls"
msExcel.Visible = False 'True is you want excel visible
msExcel.Workbooks.Open strSourceFile

...
Your Code Here
...
'Close the file, True/False Save/Don't Save
msExcel.ActiveWorkbook.Close True
'Close Excel
msExcel.Quit
Set msExcel = Nothing



This should get you close....

Carl



AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[nedstar1]

Hi AccessGuruCarl,

Thanks for the reply. I'm working on this now. . .

A question from the clearly clueless - how do I "add a reference' to Excel?

TIA

Regards,
Nedstar1

 

[PHV]

how do I "add a reference' to Excel
While in VBE, menu Tools -> References ...

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

 

[nedstar1]

Hi Friends,

Your genius knows no bounds - the debugger eventually swallowed it all in the VBE, and I saved and assigned the code to the click event of a button in access for a quick test.

Drum roll . . . . . drat.

The debugger hits a snag on this line:
Set msExcel = GetObject(Class:="Excel.Application")

With a runtime error 429, which my brief research describes as related to 'late binding,' a term so unfamiliar it makes my brain throb.

Here's the code as compiled:
Dim msExcel As Excel.Application
Dim strSourceFile As String
Set msExcel = GetObject(Class:="Excel.Application")
strSourceFile = "C:\Dev\test.xls"
msExcel.Visible = False 'True is you want excel visible
msExcel.Workbooks.Open strSourceFile

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;

http://www.ny.frb.org/markets/fxrates/noon.cfm",

Destination:=Range("A1" _
))
.Name = "noon"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "19"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'Close the file, True/False Save/Don't Save
msExcel.ActiveWorkbook.Close True
'Close Excel
msExcel.Quit
Set msExcel = Nothing
End Sub

Can anyone troubleshoot? Give me a hint as to haw to deal with 'late binding'?

TIA, friends.

Regards,
Nedstar1

 

[AccessGuruCarl]

Nedstar1,
Sorry, I posted the wrong code.

Change this...
Set msExcel = GetObject(Class:="Excel.Application")
To

Set AppExcel = CreateObject("Excel.Application")

OR Try
Set msExcel = GetObject("Excel.Application")

This should take care of the error
If it doesn't let me know, I'll try searching for my exact code!

Here is a link to a quick search I did on the forum.
Maybe it will help.
thread707-931346
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.