What is the best way to import text file to Excel?

[egghi]

I am working on a reconciliation project. Basically, we download a spool file named "Claim Trans 20060724.txt" from our system and save it as a text file in a central location daily.

There are two challenges I ran into:

1) how do I write the VBA so that Excel will import the text file which has today's date?

2) what will be the best way to set the parameters for the text import wizard so that the text file will be parsed correctly and imported into Excel?

Thanks in advance!

 

[PHV]

2) The macro recorder is your best friend.
1) You may consider the Dir, Format and Date functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

    With YourSheet.QueryTables(1)
        .Connection = _
        "TEXT;\\someserver\[b]Claim Trans " & Format(Date, "yyyymmdd") & ".txt"[/b]
' other properties.....
        .Refresh BackgroundQuery:=False
    End With

Skip,

_[red][/red]

 

[SkipVought]

Oops....

I failed to add that this is Data/Import Text...



Skip,

_[red][/red]

 

[RonRepp]

Try this:

I agree with recording a macro to ensure that you get the right array formulae, but this is basically what the code will look like:

Sub OpenATextFile()
Dim M As String
Dim D As String
Dim Y As String

M = Month(Date - 1)
D = Day(Date - 1)
Y = Year(Date - 1)
If CInt(M) < 10 Then
    M = "0" & M
End If
If CInt(D) < 10 Then
    D = "0" & D
End If
Y = Right(Y, 2)

''concatenate your date string within the filename
    Workbooks.OpenText FileName:="F:\FUSION\REPORTS\sdscan\SEND\CM" & M & D & Y & ".txt", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
End Sub

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[egghi]

Thank you all so much! I am going to give all them a try and let you know how they work!!

Thank you!!

 

[SkipVought]

Do you want to create a NEW workbook for each day? -- Ron's code

Do you want to have one table that always refers to the current day's data? -- Use Data/Import Data


Skip,

_[red][/red]

 

[egghi]

Dear Skip & Ron,

I need to create a new book each day, and Ron's code worked out perfect for my case. Thank you both very much!!!