Can I import xls data to Access over http using vb?

[Robin]

Hi

I'm developing an Access App which pulls data from various sources and
compiles summary reports. Some of the data sources are Excel files on
network folders and I can use "DoCmd.TransferSpreadsheet acImport" to import
these to a table. However, some of the sources are Excel files on internal
SharePoint sites, and have an http://.... address.

Is there a VBA method for importing data over http? I've obviously tried
"DoCmd.TransferSpreadsheet acImport" but this generates an error: 3651 -
invalid internet address.

Many thanks

Robin

 

[Alex Dybenko]

Hi,
suggest that you first download excel file using this API:
http://www.mvps.org/access/modules/mdl0037.htm
and then import it from local drive

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Robin]

Thanks Alex.

The code (and your suggestion to download to a local drive) makes perfect
sense - the API addin, I'm less sure about just through my lack of
knowledge.

Once imported, does the addin remain embedded in the application on
distribution or would this need to be added for each user? If it needs to
be added for each user I might need to seek an alternative solution.

Thanks again,

Robin

 

[Douglas J Steele]

The add-in would need to be added for each user.

 

[Robin]

Ok, many thanks Doug.

I've been wondering if there might be a COM alternative that doesn't need an
add-in, such as using Excel as an intermediary to open the remote file and
save it locally, or possibly use of DDE to extract the file content from
Excel. I need to do some investigation.

Regards,

Robin

 

[Douglas J Steele]

You could always simply include the necessary code from the add-in as a
module in your application.

 

[Robin]

Ah - a nugget of wisdom! Didn't think of that. Of course!

I'm a bit of a beginner in this whole API area but it sounds straightforward
enough (maybe naive on my part). I'll try it and report back.

Many thanks Doug!

Regards

Robin

 

[Robin]

Wow - it works!

After saying it seemed relatively simple, when I looked at the task
initially I thought this was all too much for a relative beginner. I fell
at the first hurdle when the calling sub does:

Dim objHTTP As InetTransferLib.HTTP

There was no InetTransferLib object of course. Then I realised the Library
code needed to be in a Class Module and it all fell into place!

(Shame about the pop-up, but I understand the logic and etiquette.)

Thank you very much Doug (and to Dev Ashish and Terry Kreft or course).

Regards,

Robin

 

[Robin]

Having spent an age solving this using the "InetTransferLib" add-in, a
colleague has today pointed out the equivalence of:

http://SomeServer/SomeFolder/SomeFile

and

\\SomeServer\SomeFolder\SomeFile

.... for internal SharePoint sites (which is what I was trying to access). I
can now go back to the original "DoCmd.TransferSpreadsheet acImport" option
and dump the Class Module! This also allows me to continue using
FileSystemObject DateLastModified (which I couldn't do using the HTTP Class
Module).

Wisdom is a wonderful thing

R