FTP Using TransferSpreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First off, I would like to say that I'm very new to VB, this is probably
something easy that I'm just missing.

I'm currently using the following line to FTP a spreadsheet to another
computer.

DoCmd.TransferSpreadsheet acExport, , "tablename", _
"ftp://account:password@computer/" & rstUserInfo(0) & "-" & strFileDate &
"-" & strUpCount & ".xls", False

rstUserInfo(0) = username
strFileDate = current date
strUpCount = upload count

It autocreates a filename like "Sim-1-041006-1.xls" If a file with the same
name already exists it displays a run-time error 3027 stating that the object
is read only. I would instead like it to increment the strUpCount by one and
try again.

How would I go about handling this error?

Thanks,
ToddC
 
Hi Todd,

You could trap the error, along these lines (air code):

Dim BaseName As String
Dim UpCount As Long
Dim ErrNumber As Long
Dim ErrDescription As String
Dim ErrSource As String

BaseName = "ftp://blah-blah-blah"
UpCount = 1

Do
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"AddrNew", BaseName & UpCount & ".xls", False
ErrNumber = Err.Number
ErrDescription = Err.Description
ErrSource = Err.Source
On Error GoTo ErrorHandler:
Select Case ErrNumber
Case 0 'No Error: exported OK
Exit Do
Case 3027 'Increment the number
UpCount = UpCount + 1
Case Else
Err.Raise ErrNumber, ErrSource, ErrDescription
End Select
DoEvents
Loop
 
The code worked fine when I was using the FTP site local on my box during
testing. When I try to go to a remote site MSAccess just hangs. I have
verified that the remote FTP site is accepting files using DOS FTP.

I have tried using a DNS address and an IP address. Do I have add something
so that the TransferSpreadsheet works or is there another way to FTP query
results?

DoCmd.TransferSpreadsheet acExport, , "tablename", _
"ftp://account:password@computer/" & rstUserInfo(0) & "-" & strFileDate &
"-" & strUpCount & ".xls", False

The three variables are used to build the filename.
rstUserInfo(0) = username
strFileDate = current date
strUpCount = upload count

Thanks,
ToddC
 
Hi Todd,

I was pretty surprised when you said in your first post
I'm currently using the following line to FTP a spreadsheet to another
computer.
because I thought that was impossible. But I assumed that somehow it was
working for you, and your question was about VBA, so I answered it
without comment.

Anyway: if you can't get Access to ftp you're in good company. Normal
practice is to create the file on a local drive and then ftp it to its
destination. There was a thread in microsoft.public.access.modulesdaovba
last week, subject "ftp in Access": that should get you started.
 
Back
Top