Import/Link via FTP

[JD316632]

Is it possible either by macro or VBA to import an excel document that is on a FTP site. We have a report that is ran daily that is in Excel, I take the report and add the new information to a access table, then link the excel doc and run an update query to update all the records.

Currently I have a macro in place that pulls the report from my desktop and imports it and then adds it as a link table.

Any help is appreciated, thanks!

 

[Mr. B]

JD316632,

It is definately possible to download files from an FTP site or to upload files to an FTP site.

Here is a link that may help:
http://www.smccall.demon.co.uk/Downloads.htm#FTPClient

There is a small downloadabel database file there that should provide some sample code.

HTH

 

[JD316632]

The database is unable to connect to my FTP site, I've tried using the internal and external address.

Run time error 5
Invalid procedure call or argument

 

[Mr. B]

This error code appears to indicate that there is something wrong with your code and not necessarily your connection to the server.

You may need to post your code.

 

[JD316632]

I'm just using the database you linked me to download and am putting in my ftp credentials.

 

[Mr. B]

I have not actually used that specific database but I have used code like it.

Check to see if you need or do not have any required References. In the Visual Basic Editor window, go to Tools/References. If you see andy "Missing" references you many need to select the one that you have on your system to replace the one that is missing.

 

[JD316632]

I have a feeling this is way over my head, is there a way to specify in a macro to use the current username of the person logged in?

So it will look for the excel doc on their desktop? (C:/Documents and Settings/USERNAME/Desktop)

I've tried CurrentUser() but it just returns Admin

 

[Mr. B]

I downloaded and tested the database file from the link. It works exactly as expected when you get the parameters set correctly.

First, there is VBA code involved here. Are you familiar enought with VBA to try to work with it?

When you open that demo database locate the "frmUpDownTest" form. Open it in design mode.

Next, locate the Download button. Select it. Display the Properties dialog box for this control (press the F4 key). Select the "Event" tab. Place your cursor in the "On Click" event row. Click the button at the right end of that row (the one with the Three dots on it). The VBA window will be presented and your cursor will be in the On Click event of the "btnDownload" control.

Locat the following line:

.OpenServer "[URL="ftp://ftp.microsoft.com/"][COLOR=#0000ff]ftp.microsoft.com[/COLOR][/URL]", "anonymous", "[EMAIL="someone@somewhere.com"][COLOR=#0000ff]someone@somewhere.com[/COLOR][/EMAIL]"

Change that line to read"
.OpenServer "ftp.casst.com", "Casst_DL", "E8DY6NNZ"

I have placed a test file in one of my FTP sites for your to test downloading. The file is named "MyTestExcelWorkbook.xls"

Now, save the code in the VBA window.

Open the "frmUpDownTest" form (not in design mode this time).

In the Local File Name text box provide a complete path, including the file name to where you and the downloaded file to be saved.

In the "Remote Folder" text box just leave the period.

In the "Remote File Name" text box enter the name of the file to be downloaded: MyTestExcelWorkbook.xls

Click the Download button.

My test Excel spreadsheet should be downloaded to the location you provided in the top text box.

I will leave this test file on the FTP site for a few days.

HTH

 

[JD316632]

It did work, I appreciate your help in all of this. I'm not sure if that will work for what I'm wanting to do, the file name changes daily and is a relatively long name (PANYNJ AE-CA Daily Opened Last 60 Days 2009-06-22.xls).

Basically what I would like to do is have the module executed by a macro that downloads the file to the desktop. This database will be used by maybe 3 people, never more than 1 person at the same time.

 

[Mr. B]

If you know the method by which the file is being named or any way to know the filename that you expect, then you can programmatically have the download function to look for that specific file.

 

[JD316632]

If you know the method by which the file is being named or any way to know the filename that you expect, then you can programmatically have the download function to look for that specific file.

Will always be today's date in the same format. I have a macro that pulls the excel doc into access once its on my desktop using the file name:

="C:\Documents and Settings\JHenson\Desktop\PANYNJ AE-CA Daily Opened Last 60 Days " & Format(Date(),"yyyy-mm-dd") & ".xls"

 

[Mr. B]

Using the same code that is in the On Click event of the Download button you can just use your current statement that creates the file name to assign that filename to a string type variable and then use that variable in place of the "Me.txtRemoteName" part of the code.