Impoer text file

[Boris7265]

Hi Everybody ,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have small problem and I hope somebody will be able to help me . I am not very knowledge in the vb so here is problem .
Every day I imported external data out of txt file into spreadsheet .I created macro and the code is below:
<o> </o>
"TEXT;Y:\BorisGomel\NAN\email attachment\CAPNAN-MAY2011\A1940660.TXT", _<o></o>
Destination:=Range("A1"))<o></o>
.Name = "A1940660"<o></o>
.FieldNames = True<o></o>
.RowNumbers = False<o></o>
.FillAdjacentFormulas = False<o></o>
.PreserveFormatting = True<o></o>
.RefreshOnFileOpen = False<o></o>
.RefreshStyle = xlInsertDeleteCells<o></o>
.SavePassword = False<o></o>
.SaveData = True<o></o>
.AdjustColumnWidth = True<o></o>
.RefreshPeriod = 0<o></o>
.TextFilePromptOnRefresh = False<o></o>
.TextFilePlatform = 437<o></o>
.TextFileStartRow = 1<o></o>
.TextFileParseType = xlFixedWidth<o></o>
.TextFileTextQualifier = xlTextQualifierDoubleQuote<o></o>
.TextFileConsecutiveDelimiter = False<o></o>
.TextFileTabDelimiter = True<o></o>
.TextFileSemicolonDelimiter = False<o></o>
.TextFileCommaDelimiter = False<o></o>
.TextFileSpaceDelimiter = False<o></o>
.TextFileColumnDataTypes = Array(1, 9, 1, 1, 1, 1, 1, 1, 1, 9)<o></o>
.TextFileFixedColumnWidths = Array(4, 1, 8, 20, 26, 16, 14, 13, 20)<o></o>
.TextFileTrailingMinusNumbers = True<o></o>
.Refresh BackgroundQuery:=False
<o> </o>
Everything is working fine , but there is another file txt every day so I have to change in Y:\BorisGomell\NAN\email attachment\CAPNAN-MAY2011\A1940660.TXT part A19406600 every day due to another name of text file and in the next month I have to change CAPNAN-MAY2011 on JULE2011.I do it in the code , but the there a few users that use this spreadsheet and they are really users , they can not change code . As I said before I have very superficially knowledge in VB Excel and I am really appreciated if somebody point me out how I can program message box that will ask what text file to import and once text file is provided it will change accordingly directly part of A1940660 and leave the same rest of it .
<o> </o>
Thank you,
<o> </o>
The Best Reagrds.

 

[Dartagnan]

Here is a thought. You could prompt for the file name as well as the month.

Here is an example of prompting for the file name.

Range("a1:az5000").ClearContents

Dim file As String
'Dim loc As String
'Dim ext As String

'loc = "c:\"
'ext = ".txt"
file = "c:\" + InputBox("Enter File Name", "Enter File Name", "") + ".txt"

With ActiveSheet.QueryTables _
.Add(connection:="TEXT;" + file, Destination:=Range("A1"))
.Name = "A1940660"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 9, 1, 1, 1, 1, 1, 1, 1, 9)
.TextFileFixedColumnWidths = Array(4, 1, 8, 20, 26, 16, 14, 13, 20)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[Boris7265]

Thank you very much Dartagnar .

I have made a little bit modification of your code such as :

Dim Filename As String
Filename = InputBox("Enter the file name")



With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\BorisGomell\NAN\email attachment\CAPNAN-MAY2011\" + Filename + ".TXT",

and it is working beatufully .

Thank you again and really appreciate you help .

Just one small things if you are not so tired of me .I insert a new sheet every day to import the text file .Can I programm or puit this into vb code this as well .I have the following code:

Dim Filename As String
Filename = InputBox("Enter the file name")

Worksheets.Add().Name = Filename

It is working as well nicely , but I need the new sheet to be active that The import code can work on it and import the file onto new worksheet that just was added to the workbook .I am working on it but so far I have gotte no success.

Thnak you for advice.

The best Reagard.

BorisGomel

 

[Dartagnan]

I think the following code will do what you are looking for. A new worksheet is created with the file name used to name the worksheet.

Sub import()

Dim file As String
Dim location
file = InputBox("Enter File Name")
Worksheets.Add().Name = file
location = "c:\" + file + ".txt"

Sheets(file).Select

With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;" + location, Destination:=Range("A1"))
.Name = "A1940660"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 9, 1, 1, 1, 1, 1, 1, 1, 9)
.TextFileFixedColumnWidths = Array(4, 1, 8, 20, 26, 16, 14, 13, 20)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

 

[Boris7265]

Thank you very much Dartagnan!!!

Just Wow!!!

The code is working!!!

The Best Regards.

Happy Holidays

BorisGomel