Downloading multiple files

tekman · #1 11-18-2005, 12:56 AM

I am trying to write a macro that will download csv files from an online database automatically.

Each of these files has a common file name with a date stamp and a time stamp in the file name:
"filename_20051117_000236.csv"

I have been able to open a file using a macro with a fixed file name like this:

> Sub Test()
> Workbooks.Open Filename:="ftp://user:pw@IP.IP.IP.IP/dataextracts/folder/filename"
> End Sub

What I would like to do (if possible) is to open several files using a loop, but the last digits (time stamp) are not repeatable, as the files are dumped "around" a specific time.

Is there a way to use a loop to d/l several days worth of data, in other words: increment the datestamp, but use a wildcard or something for the timestamp?

Thanks for the help

Lee

#2 11-18-2005, 04:20 AM

we have our FTP sites mapped to a logical drive...which means that they can
be accessed through our applications quite easily.
Once that's done, its quite easy to use the DIR() function -

Option Explicit

Const filepath = "H:\Excel_Demos\"
Const filenameroot = "filename_YYYYMMDD_*.CSV"

Public Sub MAIN()
Dim thisdate As Date
thisdate = Date
OpenCSV Replace(filenameroot, "YYYYMMDD", Format$(thisdate, "YYYYMMDD"))
End Sub

Private Sub OpenCSV(sFile As String)
Dim fn As String
Dim WB As Workbook
fn = Dir(filepath & sFile)
Do Until fn = ""
Set WB = Workbooks.Open(filepath & fn)
'
'process file
ProcessWB WB
WB.Close False

'next file
fn = Dir()
Loop

End Sub
Private Sub ProcessWB(WB As Workbook)
'do stuff
End Sub

"tekman" wrote:

>
> I am trying to write a macro that will download csv files from an online
> database automatically.
>
> Each of these files has a common file name with a date stamp and a time
> stamp in the file name:
> "filename_20051117_000236.csv"
>
> I have been able to open a file using a macro with a fixed file name
> like this:
>
> > Sub Test()
> > Workbooks.Open
> Filename:="ftp://user:pw@IP.IP.IP.IP/dataextracts/folder/filename"
> > End Sub
>
> What I would like to do (if possible) is to open several files using a
> loop, but the last digits (time stamp) are not repeatable, as the files
> are dumped "around" a specific time.
>
> Is there a way to use a loop to d/l several days worth of data, in
> other words: increment the datestamp, but use a wildcard or something
> for the timestamp?
>
> Thanks for the help
>
> Lee
>
>
> --
> tekman
> ------------------------------------------------------------------------
> tekman's Profile: http://www.excelforum.com/member.php...o&userid=28843
> View this thread: http://www.excelforum.com/showthread...hreadid=486139
>
>

#3 11-18-2005, 07:10 AM

Lee,

I was doing something similar with a project of mine at work. Here are the basics to get it going...

--------------------------------------------------------------------------------

Sub Main()

Sheets("Sheet1").Select

Range("A1").Select

Application.ScreenUpdating = False

Call OpenFiles

Application.ScreenUpdating = True

End Sub

Sub OpenFiles()

Dim fn As Variant, f As Integer, i As Integer, counter As Integer

i = 1

fn = Application.GetOpenFilename("CSV Files,*.csv", _

1, "Select One Or More Files To Open", , True)

If TypeName(fn) = "Boolean" Then Exit Sub

For f = 1 To UBound(fn)

Debug.Print "Selected file #" & f & ": " & fn(f)

Workbooks.Open fn(f)

While i = 1

Range("A1:J1").Select

Selection.Copy

Windows("Work_basic version.xls").Activate 'Change the filename to match yours

Range("A1").Select

ActiveSheet.Paste

i = i + 1

Wend

If (f > 1) Then

While (i <= f)

Range("A1:J1").Select

Selection.Copy

Windows("Work_basic version.xls").Activate 'Change the filename to match yours

Range("A1:J1").Select

Cells(i, 1).Select

ActiveSheet.Paste

i = i + 1

Wend

End If

ActiveWindow.ActivateNext

ActiveWindow.Close False

Next f

End Sub

--------------------------------------------------------------------------------

--
Mark Ivey

UoP e-mail: wmivey@email.uophx.edu
Personal e-mail: wmivey6311@hotmail.com
"tekman" <tekman.1yoexy_1132290002.427@excelforum-nospam.com> wrote in message news:tekman.1yoexy_1132290002.427@excelforum-nospam.com...

I am trying to write a macro that will download csv files from an online
database automatically.

Each of these files has a common file name with a date stamp and a time
stamp in the file name:
"filename_20051117_000236.csv"

I have been able to open a file using a macro with a fixed file name
like this:

> Sub Test()
> Workbooks.Open
Filename:="ftp://user:pw@IP.IP.IP.IP/dataextracts/folder/filename"
> End Sub

What I would like to do (if possible) is to open several files using a
loop, but the last digits (time stamp) are not repeatable, as the files
are dumped "around" a specific time.

Is there a way to use a loop to d/l several days worth of data, in
other words: increment the datestamp, but use a wildcard or something
for the timestamp?

Thanks for the help

Lee

--
tekman
------------------------------------------------------------------------
tekman's Profile: http://www.excelforum.com/member.php...o&userid=28843
View this thread: http://www.excelforum.com/showthread...hreadid=486139

tekman · #4 11-18-2005, 08:33 AM

I appreciate all of the help, I will play with this over the weekend.

Great Site!

Lee

Welcome to the Excel Forum

Forum Statistics