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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 06:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com