Running Excel Web Query & Importing to Access

[davidmo]

Hey Everyone:
I am trying to import information from an Excel spreadsheet that uses a web query to download data. The web query automatically refreshes when the workbook is opened.

This downloaded data is then brought into access and populates certain fields.

I would like to have a button that will open up the excel workbook, download latest data and then import it into access.

I don't think the TranserSpreadsheet method will update the web query data.

Any suggestions?

DMo

 

[CautionMP]

davidmo,
I see you decided to try the WebQuery route. Good choice.

Here is a routine that you can run from Access that will update your Excel WebQuery before you do [tt]TransferSpreadsheet[/tt]. Be sure to update the workbook path/name, worksheet name, and query table name.

Sub UpdateExcelQueryTable()
Dim appExcel As Object
Dim objWorkSheet As Object
Dim objQueryTable As Object
Set appExcel = GetObject("[b]C:\QueryTable.xls[/b]")
Set objWorkSheet = appExcel.Worksheets("[b]Sheet1[/b]")
objWorkSheet.Visible = True
Set objQueryTable = objWorkSheet.QueryTables("[b]ExternalData_1[/b]")
objQueryTable.Refresh False
While objQueryTable.Refreshing
  'Do Nothing
Wend
Set objQueryTable = Nothing
Set objWorkSheet = Nothing
appExcel.Close True
Set appExcel = Nothing
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[davidmo]

CMP:
Thanks for the code.

It does refresh but when I try to transfer the information into a temp table the fields come in blank even thought the spreadsheet shows data.

Here is my code:
Dim appExcel As Object
Dim objWorkSheet As Object
Dim objQueryTable As Object

Set appExcel = GetObject("C:\testweb.xls")
Set objWorkSheet = appExcel.Worksheets("30FX-417K-650K")

Set objQueryTable = objWorkSheet.QueryTables("Webster 30 Year Fix 417K to 650K")

objQueryTable.Refresh True

While objQueryTable.Refreshing
'Do Nothing
Wend

Set objQueryTable = Nothing
Set objWorkSheet = Nothing

appExcel.Close True

Set appExcel = Nothing

DoCmd.TransferSpreadsheet acImport, , "tempIntRate", "C:\testweb.xls", False, "A6:C10"


Also is it possible to have the information that is being imported from excel overwrite the fields that are already in the table instead of them being added?

DMo

 

[CautionMP]

davidmo,
I suspect that your web query in Excel is refreshing on open. Since the refresh takes a while that may be the reason Access is not seeing any data. I use primarily Excel 2000 wich doesn't refresh the data unless you tell it to, I'm not sure what the newer versions of Excel do which may be the issue. You may need to check the help files to see if this is happening and disable if necesary.

Also is it possible to have the information that is being imported from excel overwrite the fields that are already in the table instead of them being added?

Short answer, no. Access does not have a build in mechanism for this. You can use a linked table and an [tt]Append[/tt] query to acomplish this.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[davidmo]

CMP:
It worked. I had it set to refresh on opening. Once I disabled this function the info came in.

Thanks a ton.

Hate to ask another question but how do you specify a specific worksheet within the workbook when using the TransferSpreadsheet method?

DMo

 

[CautionMP]

davidmo,
Ask away, that's what the forums are for.

It's the range argument.
In Code:
[tt]DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "NewTable", "C:\Test.xls", True, "Sheet1"[/tt]

In a macro:
[tt]Action TransferSpreadsheet
Transfer Type : Import
Spreadsheet Type : Microsoft Excel 8-9
Table Name : NewTable
File Name : C:\Test.xls
Has Field Names : Yes
Range : Sheet1[/tt]

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[davidmo]

CMP:
And if I need specific cells (A6:C10) within that a specific worksheet?

DMo

 

[PHV]

Anyway, replace this:
objQueryTable.Refresh True
with this:
objQueryTable.Refresh False

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

 

[davidmo]

PHV:
What will this do?
objQueryTable.Refresh True
with this:
objQueryTable.Refresh False


DMo

 

[CautionMP]

davidmo,
Range = [tt]Sheet1!A6:C10[/tt]

As far as the [tt]objQueryTable.Refresh True/False[/tt] I don't think it makes a difference becasue that routine opens and refreshes the web query as a seperate process, before, the data is moved with [tt]TransferSpreadsheet()[/tt]. If we were moving the data with the same routine we could use False and remove the
[tt]While objQueryTable.Refreshing
'Do Nothing
Wend[/tt]
before we actually started to move the data from Excel to Access*.

CMP

[small]*P.S. That's a little hint on one way to 'overwrite' the data in the table instead of importing. CMP[/small]

(GMT-07:00) Mountain Time (US & Canada)

 

[davidmo]

CMP:
thanks for all the help. works perfectly.

DMo