imporing comma seperated file to excel with text to column

[pals70423]

Hi All,
I am trying to automate one of my daily task which is to import a comma seperated file into a excel file using a text to column.

I have recorded the macros while doing that and got macros as follows

Sub test()

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\pals\macros\test\pos\test.log", Destination:=Range("A1"))
.Name = "test"
.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
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


i am able to achive the required excel file but the trouble is that i have keep the file as test.log.This is a daily generated file with data appended -- testddmm.log where dd is the date and mm is the month.i tried to create a variable

filename = "test" &Format(Date, "ddmm") & ".log"

but i am end up getting a error message like

runtime error 4.specified file is found

i will be having file generated daily into a folder with the date appended

like

test3003.log
test3103.log
test 0104.log

i have to pick the file generated with the current date and perform the text to column task using macros.

could anyone help me in fixing this issue.

 

[Ruddles]

Re: importing comma separated file to Excel with text to column

The code looks fine but that's just the code where you set up the filename.

Can you show us the code where you're actually using this filename?

Is it something like this:-

[FONT=Arial]"TEXT;C:\pals\macros\test\pos\" & filename[/FONT]

 

[pals70423]

Hi,
I tried changing the file name to appended with the date but still getting the same error


Run-time error '1004'

Excel cannot find the text file to refresh this external data range

check to make sure the text file has not been moved or renamed ,then try the refresh again


modified code
-------------
Sub Macro1()
'
' Macro1 Macro
'
strFileName = "test" & Format(Date, "ddmm") & ".log"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\pals\macros\test\pos\" & strFileName, Destination:=Range("A1"))
.Name = "& strFileName"
.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
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Ruddles]

.Name = "& strFileName"

Ah!

.Name = strFileName

 

[pals70423]

Hi,
Still getting the same error

I have recorded the importing task into a macro which is saved as function in module (test1).i am taking the function and copying to new excel file under module1 and calling the module using thr below syntax


Public Sub workbook_open()
Module1.test1
End Sub


kindly let me know if the process i am following is correct

 

[Ruddles]

Did you change:-

.Name = "& strFileName"

to:-

.Name = strFileName

?

If you've generated a new set of VBA code, you'll need to post the new code.

 

[pals70423]

Hi Ruddles,
I have changed the values you have mention.find below the modified code

Sub test1()
'

strFileName = "test" & Format(Date, "ddmm") & ".log"
MsgBox (strFileName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\pals\macros\test" & strFileName, Destination:=Range("A1"))
.Name = strFileName
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


i am calling the function in the public sub workbook_open()


Public Sub workbook_open()
Module1.test_saveback
End Sub

 

[pals70423]

the function is called with below content

Public Sub workbook_open()
Module1.test1
End Sub

 

[Ruddles]

Are you sure the filename is correct? After you set up the filename, insert a line to check it exists:-

strFileName = "[COLOR=red]test[/COLOR]" & Format(Date, "ddmm") & ".log"
"TEXT;C:\pals\macros\[COLOR=red]test[/COLOR]" & strFileName, Destination:=Range("A1"))

You have the word "test" in there twice. That doesn't seem right.

 

[pals70423]

Hi Ruddles,
i have checked the file name using the msgbox and it shows the correct file 'test0406.log'.below is the code which i am using for the process.Once i give bedug the below lines are highlighted.

.Refresh BackgroundQuery:=False


code :
-----
Sub test1()
'

strFileName = "test" & Format(Date, "ddmm") & ".log"
MsgBox (strFileName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\pals\macros\test\pos" & strFileName, Destination:=Range("A1"))
.Name = strFileName
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


is there any other way to covert the csv file to excel file apart from recording the macros.