ftp paths defined in variables are persisting. How to clear?(vba)

[CharlieH]

I've got a routine that a text file from an ftp site, converts it into an
excel spreadsheet, and saves it back to the ftp site. This routine loops
through multiple files and ftp sites. The ftp site paths are listed in a
table and are picked up and defined as variables. Although the routine
succesfully opens the right files from the right sites, it keeps using the
first ftp site for every iteration of the save portion of the routine (like
there's a cache that can't be cleared out or something). I could hard code
the paths, but I like having them in a spreadsheet. Thoughts?

 

[Bob Bridges]

I'm with you; you should be able to do it easily enough. Guess we'll have to
see your code, or at least the outer part of your loop that runs through the
URLs and opens each one in turn.

(Disclaimer: I have done FTP from VBA/Access and VBS, and I don't suppose
VBA/Excel is any different. But I've never done multiple FTP sites in one
VBA session, so I can't swear you're mistaken in principle. Guess we'll find
out.)

 

[CharlieH]

Thx, Bob. Here are the relevant pieces:

Dim vblTargetFile
Dim vblSaveFilePath

Sub Define_Variables_Schedule_Run()
Sheets("Schedule").Select
Range("A1").Select (other code lines drop the cursor down)
vblSaveFilePath = ActiveCell.Offset(0, 9).Value
vblTargetFile = ActiveCell.Offset(0, 10).Value
Call SaveFile
End Sub

Sub SaveFile()
ActiveWorkbook.SaveAs Filename:= _
vblSaveFilePath & "/" & vblTargetFile & " " & Year(vblPostingDate) &
Format(Month(vblPostingDate), "00") & Format(Day(vblPostingDate), "00") &
".xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

Again, it all works, but when I loop through to do the next save, it keeps
using the 1st ftp site. I know it's capable of connecting to different sites,
because each file it opens is from different (and correct) sites. I'm
wondering if there's a cache or a history or something I need to know about
(these are secure sites utilizing user names and passwords).

In the short run, I can create separate workbooks for each job I need to
run; not my preference, though. (I'm going to end up with a lot of workbooks!)

Thanks

 

[Bob Bridges]

Well, you're using a forward slash instead of a backslash in the SaveAs call;
I don't see how that would cause the symptoms you mention, but I thought I'd
mention it in case it's causing some other problem.

But you didn't show me the part I wanted to see -- this seems to be the
inner logic, where you save the text file after it's been converted to a
spreadsheet, but the part that's causing you the problem (apparently) is the
outer part, the loop logic where it selects the location to store it. This
is just a hardcoded example where it saves the currently active workbook to a
path and filename specified in its own I1 and J1, but if I understood you the
macro actually loops through a series of filenames in a table. Where's that
logic?

Thx, Bob. Here are the relevant pieces:

Dim vblTargetFile
Dim vblSaveFilePath

Sub Define_Variables_Schedule_Run()
Sheets("Schedule").Select
Range("A1").Select (other code lines drop the cursor down)
vblSaveFilePath = ActiveCell.Offset(0, 9).Value
vblTargetFile = ActiveCell.Offset(0, 10).Value
Call SaveFile
End Sub

Sub SaveFile()
ActiveWorkbook.SaveAs Filename:= _
vblSaveFilePath & "/" & vblTargetFile & " " & Year(vblPostingDate) & _
Format(Month(vblPostingDate), "00") & _
Format(Day(vblPostingDate), "00") & ".xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

Again, it all works, but when I loop through to do the next save, it keeps
using the 1st ftp site. I know it's capable of connecting to different sites,
because each file it opens is from different (and correct) sites. I'm
wondering if there's a cache or a history or something I need to know about
(these are secure sites utilizing user names and passwords).

I'm with you; you should be able to do it easily enough. Guess we'll have to
see your code, or at least the outer part of your loop that runs through the
URLs and opens each one in turn.

I've got a routine that [gets?] a text file from an ftp site, converts it
into an excel spreadsheet, and saves it back to the ftp site. This
routine loops through multiple files and ftp sites. The ftp site paths
are listed in a table and are picked up and defined as variables.
Although the routine succesfully opens the right files from the right
sites, it keeps using the first ftp site for every iteration of the save
portion of the routine (like there's a cache that can't be cleared
out or something). I could hard code the paths, but I like having
them in a spreadsheet. Thoughts?