D_Marcel
07-28-2017, 12:04 PM
Hello VBAX friends!
I'm facing a very strange issue when importing "*.csv" files to Excel without open them. Basically, I need to import always the newest file of a directory, so after writing the desired instructions, I'm performing the following test:
1. Import the only file in the directory, let's call it "original file".
2. Open it, delete some rows and save it in the same directory, only to ensure that the macro will import this file, as this file is now the newest one.
Step 1 - CSV imported successfully;
Step 2 - The "raw data" of the CSV file is placed in the first column, but is not being converted from text to column.
I've read about all the properties of QueryTable at MSDN and performed some changes in the code, but the error persists.
After got the newest file:
On Error GoTo ERROR_HANDLING1
LV_NEWEST_FILE = NEWESTFILE(LV_FILE_PATH, "*.csv", "Path")
Call IMPORT_TXT_FILE(LV_NEWEST_FILE, GV_PREVIEW_TABLE)
I'm calling a sub only to import the CSV:
Sub IMPORT_TXT_FILE(ByVal SOURCE_PATH As String, TARGET_SHEET As Worksheet)
TARGET_SHEET.Select
With TARGET_SHEET.QueryTables.Add(Connection:="TEXT;" & SOURCE_PATH, DESTINATION:=Range("A1"))
.Name = "CAPTURE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I was trying right now to provide some samples, but the original file has more than 100.000 rows of confidential data, and if I delete them in order to allow a test and/or debugging, we will fall in the step 2, because as I said, any modifications in the original file causes the conversion from text to columns to not work.
Anyone has some idea of why this is happening, or alternative ways to import the CSV file without opening it?
Thanks a lot!
Regards,
Douglas
I'm facing a very strange issue when importing "*.csv" files to Excel without open them. Basically, I need to import always the newest file of a directory, so after writing the desired instructions, I'm performing the following test:
1. Import the only file in the directory, let's call it "original file".
2. Open it, delete some rows and save it in the same directory, only to ensure that the macro will import this file, as this file is now the newest one.
Step 1 - CSV imported successfully;
Step 2 - The "raw data" of the CSV file is placed in the first column, but is not being converted from text to column.
I've read about all the properties of QueryTable at MSDN and performed some changes in the code, but the error persists.
After got the newest file:
On Error GoTo ERROR_HANDLING1
LV_NEWEST_FILE = NEWESTFILE(LV_FILE_PATH, "*.csv", "Path")
Call IMPORT_TXT_FILE(LV_NEWEST_FILE, GV_PREVIEW_TABLE)
I'm calling a sub only to import the CSV:
Sub IMPORT_TXT_FILE(ByVal SOURCE_PATH As String, TARGET_SHEET As Worksheet)
TARGET_SHEET.Select
With TARGET_SHEET.QueryTables.Add(Connection:="TEXT;" & SOURCE_PATH, DESTINATION:=Range("A1"))
.Name = "CAPTURE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I was trying right now to provide some samples, but the original file has more than 100.000 rows of confidential data, and if I delete them in order to allow a test and/or debugging, we will fall in the step 2, because as I said, any modifications in the original file causes the conversion from text to columns to not work.
Anyone has some idea of why this is happening, or alternative ways to import the CSV file without opening it?
Thanks a lot!
Regards,
Douglas