imporing comma seperated file to excel with text to column

pals70423

New Member
Joined
Feb 6, 2011
Messages
25
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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:-
Code:
[FONT=Arial]"TEXT;C:\pals\macros\test\pos\" & filename[/FONT]
 

pals70423

New Member
Joined
Feb 6, 2011
Messages
25
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
 

pals70423

New Member
Joined
Feb 6, 2011
Messages
25

ADVERTISEMENT

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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Did you change:-
Code:
.Name = "& strFileName"
to:-
Code:
.Name = strFileName
?

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

pals70423

New Member
Joined
Feb 6, 2011
Messages
25

ADVERTISEMENT

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

New Member
Joined
Feb 6, 2011
Messages
25
the function is called with below content

Public Sub workbook_open()
Module1.test1
End Sub
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Are you sure the filename is correct? After you set up the filename, insert a line to check it exists:-
Code:
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

New Member
Joined
Feb 6, 2011
Messages
25
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top