Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading multiple files
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 /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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading multiple files
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 /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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading multiple files
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: Personal e-mail: "tekman" wrote in message ... 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 /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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading multiple files
I appreciate all of the help, I will play with this over the weekend. Great Site! Lee -- tekman ------------------------------------------------------------------------ tekman's Profile: http://www.excelforum.com/member.php...o&userid=28843 View this thread: http://www.excelforum.com/showthread...hreadid=486139 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Downloading files from the the Internet | Excel Worksheet Functions | |||
Note on downloading XL2007 files | Excel Discussion (Misc queries) | |||
Automatic Downloading of files (PDF or HTML) using Excel | Excel Programming | |||
Downloading Files Using Excel | Excel Discussion (Misc queries) | |||
why do i have to keep downloading excell when i open files? is th. | New Users to Excel |