PDA

View Full Version : Loading text file with specific name in excel



wes2706
03-30-2017, 01:40 PM
I will need to load a text file into excel into a particularworksheet called “Temp_Cal” in cell “A1”… the text file should be loaded as delimitedand the delimiters are tab and space only.
As an input I have the folder location in an excel cell…letssay Sheet1 in cell C3
And the file name is also in an excel cell lets say Sheet1in cell C4
Using the values in Sheet1 cell C3 and Sheet1 Cell C4 as my input, I need to create a string with the file name and location, then openthe text file and insert it into Excel Sheet (“Temp_Cal”) from Cell “A1” onwards…
Can someone please help?

mdmackillop
03-31-2017, 03:45 AM
Use the macro recorder to determine data types etc. and substitute accordingly.

Sub Imports()
Dim Conn As String


With Sheets("Sheet1")
Conn = "TEXT;" & .Cells(3, 3) & "\" & .Cells(4, 3)
End With

With Sheets("TempCal")
With .QueryTables.Add(Connection:=Conn, Destination:=.Range("$A$1"))
.Name = "TextData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1257
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
'.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End With
End Sub