Skipping Import Text File dialog in a Macro

[Leon]

I tried the following but the dialog always appears and forces me to click
the Import button.

Sheets("Sheet1").Select
Application.DisplayAlerts = False
With Selection.QueryTable
.Connection = _
"TEXT;C:\Edrive\standalone\Lineup\18ScorecardRoster.txt"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2)
.TextFileFixedColumnWidths = Array(4)
.Refresh BackgroundQuery:=False
End With
Sheets("Scorecard-v").Select

 

[Joel]

I'm not usre, but I think it is because you are using Fix Width data. I
don't get the pop up when I'm using Delimited Data. try re-recording a macro
and use a Delimiter rather than fixed width. If you need fixed width data
then I have a macro that opens a text file and export the cells one at a time.

 

[Leon]

Thanks Joel, you were right. Could you help me with one other thing (I am
very new at this)? How can I clear the "save" flag so exiting won't prompt
the user to save?

 

[Joel]

I don't think you can turn off the save flag when exiting excel. You can in
a macro
make the savechanges either true or false. False will eliminate the prompt.

Workbooks("BOOK1.XLS").Close SaveChanges:=False

 

[Leon]

How can I convert the fixed string file path to a variable? I tried the
following:
Dim fileDir As String
fileDir = ThisWorkbook.Path
Dim fileName As String
fileName = fileDir & "\Release\18ScorecardRoster.txt"
Sheets("Sheet1").Select
With Selection.QueryTable
.Connection = _
"TEXT;fileName"

 

[Joel]

whan combining strings you must use the & character


Dim fileDir As String
fileDir = ThisWorkbook.Path
Dim fileName As String
fileName = fileDir & "\Release\18ScorecardRoster.txt"
Sheets("Sheet1").Select
With Selection.QueryTable
.Connection = _
"TEXT;" & fileName