How to open .csv files?

Macro help with importing csv files into a template

• QUESTION: I am trying to take data from multiple csv files and enter them into a template I already made. First I prompt the user to tell me where all CSV files are on their computer. Is there way of asking for the first file and a macro will save the file directory and find the next 3 files needed? The code belows allows the user to select all 4 CSV files need for the template but it opens the files in individual sheets not the template. How do I place them into the template beside recording a macro and cutting and pasting? Sub GetOpenFileName() Dim vFilename As Variant Dim sPath As String Dim lFilecount As Long Dim lCount As Long sPath = "c:windows" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Please select All CSV file(s) to import", , True) If TypeName(vFilename) = "Boolean" Then Exit Sub For lCount = 1 To UBound(vFilename) ImportThisOne CStr(vFilename(lCount)) Next End Sub Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy new sheet into this workbook oBook.Worksheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub Thank you for you help ANSWER: Sara, Opening them in their own workbook and then copying the data is the easiest by far way to do it. However, you don't need to copy the sheets into your workbook. You can paste the data into your template. Sub GetOpenFileName() Dim vFilename As Variant Dim sPath As String Dim lFilecount As Long Dim lCount As Long sPath = "c:windows" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Please select All CSV file(s) to import", , True) If TypeName(vFilename) = "Boolean" Then Exit Sub For lCount = 1 To UBound(vFilename) ImportThisOne CStr(vFilename(lCount)) Next End Sub ' this has been altered to copy the data from the four files to the template sheet Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Dim tmplt as Worksheet Dim r as Range ' assume your template sheet is in thisworkbook and has the name Data set tmplt = Thisworkbooks.Worksheets("Data") Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy data into template set r = oBook.Worksheets(1).Range("A1").UsedRange r.Copy tmplt.cells(tmplt.rows.count,1).end(xlup).offset(1,0) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub if you want to have the user select one file, then yes, you can parse the folder from that file. But you don't give enough information on how I should find the other 3 files. First, are they going to be the only files in that directory? If so, then that is enough information. If not, then tell me how I can identify the files to bring in. Also, I assume I am appending all the data in the template - so 4 CSV files become one database in a template sheet. So is there a particular order you need the files brought in - I assume not since you don't show any order relevance in your existing code. Anyway, if you can tell me more about identifying what files to bring in, I can give you some further code modifications. ---------- FOLLOW-UP ---------- QUESTION: Hey If I don't have all 4 CSV files the code gives me an error of 400. Is there a way to slightly change the code to allow for 1 to all four files imported? So if the code doesn't find one of the csv file (like LOSS.csv) it will still fill in the data from the other files. Right now the code needs all four files to work correctly. Dim sPath As String Dim lFilecount As Long Dim lCount As Long Dim v As Variant, v1 As Variant, v2 As Variant, v3 As Variant ReportName = InputBox("Please enter Report Name:") Range("C3").Value = ReportName [c4] = Now 'date and time v = Array("TPUT.csv", "LATENCY.CSV", "LOSS.csv", "BTB.csv") v1 = Array("TPUT", "LATENCY", "LOSS", "LOSS", "BTB") v2 = Array("A1:C3", "A1:D3", "A1:F4", "G1:K4", "A1:B3") v3 = Array("A10", "A21", "A32", "A42", "A62") sPath = "c:\" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFileName("CSV files (*.csv),*.csv", , "Please select ONE CSV file(s) to import", , False) If TypeName(vFilename) = "Boolean" Then ' They pressed Cancel MsgBox "No file was selected" Exit Sub End If iloc = InStrRev(vFilename, "\", -1, vbTextCompare) sPath = Left(vFilename, iloc) For lCount = LBound(v) To UBound(v) ImportThisOne sPath & v(lCount) Next For lCount = LBound(v1) To UBound(v1) Worksheets(v1(lCount)).Range(v2(lCount)).Copy _ Worksheets("sheet1").Range(v3(lCount)) Next Application.DisplayAlerts = False Worksheets(v1).Delete Application.DisplayAlerts = True End Sub ' this has been altered to copy the data from the four files to the template sheet Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy new sheet into this workbook oBook.Worksheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub ANSWER: Sara, this is untested, but should do what you ask: Sub Openfiles() Dim sPath As String Dim lFilecount As Long, iloc As Long Dim lCount As Long, j As Long Dim ReportName As String, vFilename As Variant Dim sName As String Dim v As Variant, v1 As Variant, v2 As Variant, v3 As Variant ReportName = InputBox("Please enter Report Name:") Range("C3").Value = ReportName [c4] = Now 'date and time v = Array("TPUT.csv", "LATENCY.CSV", "LOSS.csv", "BTB.csv") v1 = Array("TPUT", "LATENCY", "LOSS", "LOSS", "BTB") v2 = Array("A1:C3", "A1:D3", "A1:F4", "G1:K4", "A1:B3") v3 = Array("A10", "A21", "A32", "A42", "A62") sPath = "c:\" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Please select ONE CSV file(s) to import", , False) If TypeName(vFilename) = "Boolean" Then ' They pressed Cancel MsgBox "No file was selected" Exit Sub End If iloc = InStrRev(vFilename, "\", -1, vbTextCompare) sPath = Left(vFilename, iloc) For lCount = LBound(v) To UBound(v) sName = Dir(sPath & v(lCount)) If sName <> "" Then ImportThisOne sPath & v(lCount) Else For j = LBound(v1) To UBound(v1) If InStr(1, v1(j), v(lCount), vbTextCompare) Then v1(j) = Empty v2(j) = Empty v3(j) = Empty End If Next j End If Next lCount For lCount = LBound(v1) To UBound(v1) If Not IsEmpty(v1(lCount)) Then Worksheets(v1(lCount)).Range(v2(lCount)).Copy _ Worksheets("sheet1").Range(v3(lCount)) End If Next Application.DisplayAlerts = False For j = LBound(v1) To UBound(v1) If Not IsEmpty(v1(j)) Then Worksheets(v1(j)).Delete End If Next Application.DisplayAlerts = True End Sub Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy new sheet into this workbook oBook.Worksheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub ---------- FOLLOW-UP ---------- QUESTION: as far as erasing old data, yes this should do that Sub Openfiles() Dim sPath As String Dim lFilecount As Long, iloc As Long Dim lCount As Long, j As Long Dim ReportName As String, vFilename As Variant Dim sName As String, r as Range, r1 as Range Dim v As Variant, v1 As Variant, v2 As Variant, v3 As Variant ReportName = InputBox("Please enter Report Name:") Range("C3").Value = ReportName [c4] = Now 'date and time v = Array("TPUT.csv", "LATENCY.CSV", "LOSS.csv", "BTB.csv") v1 = Array("TPUT", "LATENCY", "LOSS", "LOSS", "BTB") v2 = Array("A1:C3", "A1:D3", "A1:F4", "G1:K4", "A1:B3") v3 = Array("A10", "A21", "A32", "A42", "A62") sPath = "c:\" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Please select ONE CSV file(s) to import", , False) If TypeName(vFilename) = "Boolean" Then ' They pressed Cancel MsgBox "No file was selected" Exit Sub End If ' clear old data in Sheet1 set sh1 = Worksheets("Sheet1") for lCount = lbound(v1) to ubound(v1) set r = sh1.Range(v2(lCount)) set r1 = sh1.Range(v3(lCount)).Resize(r.rows.count,r.columns.count) r1.ClearContents next ' end of clear data iloc = InStrRev(vFilename, "\", -1, vbTextCompare) sPath = Left(vFilename, iloc) For lCount = LBound(v) To UBound(v) sName = Dir(sPath & v(lCount)) If sName <> "" Then ImportThisOne sPath & v(lCount) Else For j = LBound(v1) To UBound(v1) If InStr(1, v1(j), v(lCount), vbTextCompare) Then v1(j) = Empty v2(j) = Empty v3(j) = Empty End If Next j End If Next lCount For lCount = LBound(v1) To UBound(v1) If Not IsEmpty(v1(lCount)) Then Worksheets(v1(lCount)).Range(v2(lCount)).Copy _ Worksheets("sheet1").Range(v3(lCount)) End If Next Application.DisplayAlerts = False For j = LBound(v1) To UBound(v1) If Not IsEmpty(v1(j)) Then Worksheets(v1(j)).Delete End If Next Application.DisplayAlerts = True End Sub Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy new sheet into this workbook oBook.Worksheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub This code give me subscript out of range. What does this mean? It looks like Sh1 is not defined but that didn't seem to change anything. I start the macro and when I select a file with all 4 files in the folder, then the data fills the template but an error message pops up "subscript out of range" and the BTB file doesnt delete. When I select a file with only 2 files in the folder I get the same error but the data doesn't file in and both files are not deleted. I am trying to take data from multiple csv files and enter them into a template I already made. First I prompt the user to tell me where the CSV files are. The code belows allows the user to select all 4 CSV files need for the template but it opens the files in individual sheets not the template. How do I place them into the template beside recording a macro and cutting and pasting? Sub GetOpenFileName() Dim vFilename As Variant Dim sPath As String Dim lFilecount As Long Dim lCount As Long sPath = "c:windows" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Please select All CSV file(s) to import", , True) If TypeName(vFilename) = "Boolean" Then Exit Sub For lCount = 1 To UBound(vFilename) ImportThisOne CStr(vFilename(lCount)) Next End Sub Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy new sheet into this workbook oBook.Worksheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub Thank you for you help

• Answer:

  Sara, I had two separate copies of the code and I believe I pasted in the wrong copy. I am at a new location, so I revised this code from what you posted and made the necessary changes. This is tested with 4 and 2 files and worked fine for me. Sub Openfiles() Dim sPath As String, sh1 As Worksheet Dim lFilecount As Long, iloc As Long Dim lCount As Long, j As Long Dim ReportName As String, vFilename As Variant Dim sName As String, r As Range, r1 As Range Dim v As Variant, v1 As Variant, v2 As Variant, v3 As Variant ReportName = InputBox("Please enter Report Name:") Set sh1 = Worksheets("Sheet1") sh1.Range("C3").Value = ReportName sh1.Range("C4").Value = Now 'date and time v = Array("TPUT.csv", "LATENCY.CSV", "LOSS.csv", "BTB.csv") v1 = Array("TPUT", "LATENCY", "LOSS", "LOSS", "BTB") v2 = Array("A1:C3", "A1:D3", "A1:F4", "G1:K4", "A1:B3") v3 = Array("A10", "A21", "A32", "A42", "A62") sPath = "c:\" ChDrive sPath ChDir sPath vFilename = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Please select ONE CSV file(s) to import", , False) If TypeName(vFilename) = "Boolean" Then ' They pressed Cancel MsgBox "No file was selected" Exit Sub End If ' clear old data in Sheet1 For lCount = LBound(v1) To UBound(v1) Set r = sh1.Range(v2(lCount)) Set r1 = sh1.Range(v3(lCount)).Resize(r.Rows.Count, r.Columns.Count) r1.ClearContents Next ' end of clear data iloc = InStrRev(vFilename, "\", -1, vbTextCompare) sPath = Left(vFilename, iloc) For lCount = LBound(v) To UBound(v) sName = Dir(sPath & v(lCount)) If sName <> "" Then ImportThisOne sPath & v(lCount) Else For j = LBound(v1) To UBound(v1) If InStr(1, v(lCount), v1(j), vbTextCompare) Then v1(j) = Empty v2(j) = Empty v3(j) = Empty End If Next j End If Next lCount For lCount = LBound(v1) To UBound(v1) If Not IsEmpty(v1(lCount)) Then Worksheets(v1(lCount)).Range(v2(lCount)).Copy _ Worksheets("sheet1").Range(v3(lCount)) End If Next Application.DisplayAlerts = False For j = LBound(v1) To UBound(v1) If Not IsEmpty(v1(j)) Then On Error Resume Next Set sh1 = Nothing Set sh1 = Worksheets(v1(j)) If Not sh1 Is Nothing Then sh1.Delete End If On Error GoTo 0 End If Next Application.DisplayAlerts = True End Sub Sub ImportThisOne(sFileName As String) Dim oBook As Workbook Workbooks.Open sFileName Set oBook = ActiveWorkbook 'Now do your processing on the newly imported sheet 'Copy new sheet into this workbook oBook.Worksheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'close text file, do not save changes oBook.Close False Set oBook = Nothing End Sub sorry for the confusion. You can't open a text file and have it somehow simply be inside a template -- you do need to copy/paste the values into the sheets you want in the template you specify, which I assume is the "thisworkbook" you're referring to in your code. What do you have against copy/paste? As you have it, it copies the csv file into a new sheet in thisworkbook - if that's not what you want, you can copy the cells into already existing sheets. I'm not sure I understand what you're really looking for...