Re: Downloading multiple files

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxxxxxx
Personal e-mail:  wmivey6311@xxxxxxxxxxx

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@xxxxxxxxxxx/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?action="">
View this thread: http://www.excelforum.com/showthread.php?threadid=486139

Relevant Pages

  • filenames with spaces and list in a for loop
    ... Second attempt was to double quote the "`ls -1A $1`", which generates ONE huge argument to the for loop. ... Unfortunately, the list also generates a newline every 80 characters in the $LIST, so once in a while I was left with each_item being set to something like "\nfilename". ... At the first occurrence of $each_item in the function I would get something like <filename> not found. ... what if I have a directory with more than 65000 characters worth? ...
    (comp.unix.shell)
  • Re: long running perl programs & memory untilization
    ... >> It does setup stuff, and then goes into a loop. ... # reads in temporary capture file adds timestamp, ... # Argument 1 is filename that the labled image should be stored as ... my $grab = $_; ...
    (comp.lang.perl.misc)
  • Re: DTS query result to file - can I loop it?
    ... How to loop through a global variable Rowset ... How can I change the filename for a text file connection? ... The values for @MyType are stored in a database table so a cursor ... > existing DTS package or will I have to create a new package to handle each ...
    (microsoft.public.sqlserver.dts)
  • Re: python skipping lines?
    ... and your first pass through this loop, you exhaust RawData by reading to the end. ... The second pass through the UnitList loop, you pass the exhausted RawData to PullHourlyData. ... I'm thinking you'd need to RawData.seekor some such "rewind" ability. ... Part of the confusion stems from the fact that what you refer to as "filename" is actually a file object that contains state. ...
    (comp.lang.python)
  • Re: Best way to integrate all filenames in a directory with SQL Recordset?
    ... Actual filename value transform from old to new is more like Filename.1 -> ... mismatch lists if something goes wrong. ... I will see what I can do to add another loop mid-stream to catch all file ... > Your destination I can only presume has 4 attributes ...
    (microsoft.public.sqlserver.dts)