On a weekly basis I import to an excel file, 5 csv files for the last 5 days from a folder that holders numerous files (to tell each file apart the file name is the date). Within my excel spreadsheet I have set up an admin tab showing the file path of the files I need to import, shown in rows (3 to 7). The macro looks at the file names to be imported and imports to sheets names 'File 1' through to 'File 5'. I update the dates of the files I want to import under the heading 'Date of file' the macro runs fine until there is less than 5 file names showing or the file is missing from the folder I am trying to import from. I have 2 questions
1) if there is less than 5 files to be imported, how do I reflect this in my code as current the macro reads For rep = 3 To 7, I need to say if the file name is missing move onto importing the next file
2) if the the file for a date is missing from the folder how to I get the code to show a message to reflect 'date xxxx is missing from the import'?
<colgroup><col span="5"></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>Sub Test_ImportAllFiles()
For rep = 3 To 7
Dim file_name As String
Dim row_number As String
Dim output_sheet As String
file_name = Sheets("Admin").Range("A" & rep).Value
output_sheet = Sheets("Admin").Range("B" & rep).Value
row_number = Sheets("Admin").Range("C" & rep).Value
With Sheets(output_sheet).QueryTables.Add(Connection:="TEXT;" + file_name, Destination:=Sheets(output_sheet).Range("$A" + row_number))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next rep
End Sub
1) if there is less than 5 files to be imported, how do I reflect this in my code as current the macro reads For rep = 3 To 7, I need to say if the file name is missing move onto importing the next file
2) if the the file for a date is missing from the folder how to I get the code to show a message to reflect 'date xxxx is missing from the import'?
Files | Output Sheet Name | Output Row | Date of File | |
C:\Documents\Example\20160101.csv | File1 | 1 | 20160101 | |
C:\Documents\Example\20160102.csv | File2 | 1 | 20160102 | |
C:\Documents\Example\20160103.csv | File3 | 1 | 20160103 | |
C:\Documents\Example\20160104.csv | File4 | 1 | 20160104 | |
C:\Documents\Example\20160105.csv | File5 | 1 | 20160105 |
<colgroup><col span="5"></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col><col></colgroup><tbody></tbody> |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
For rep = 3 To 7
Dim file_name As String
Dim row_number As String
Dim output_sheet As String
file_name = Sheets("Admin").Range("A" & rep).Value
output_sheet = Sheets("Admin").Range("B" & rep).Value
row_number = Sheets("Admin").Range("C" & rep).Value
With Sheets(output_sheet).QueryTables.Add(Connection:="TEXT;" + file_name, Destination:=Sheets(output_sheet).Range("$A" + row_number))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next rep
End Sub