Suppress File Selection Dialog box
 

Posted before but got scrolled to page 9 with no responses.

I have some code that is importing a specific file based on some variables
input into some input boxes. The files will always have the same naming
scheme and the macro will import the correct file based on the code and
variables. My problem is that the code is opening a dialog box in order to
select the file. My code already selects the file but the user has to hit ok
in order to move on. I want to suppress this dialog box as the code should
be doing the selecting of the file. I've attempted turning the DisplayAlerts
to false and it hasn't worked to suppress the box. I've searched the board
for all of the keywords I can think of without any success. Thank you in
advance for your assistance. See code below.

Thank you,
Brian

Sheets("Sheet1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _
"TEXT;\\maninfs1\account\FINANCE\" & Year & "\" & Month &
"\Files\ShowProfit" & Year & "" & Month & ""
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(30, 8, 13, 16, 19, 17)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Suppress File Selection Dialog box
 

Brian,

Try adding ".TextFilePromptOnRefresh = False" to your list of arguments for
the QueryTable.

i.e.

With Selection.QueryTable
' SKIP LINES TO CLIP POST SIZE - leave your old lines here!
.Refresh BackgroundQuery:=False
.TextFilePromptOnRefresh = False ' new line
End With
End With

Regards,

George

"BCBarney" wrote:

Posted before but got scrolled to page 9 with no responses.

I have some code that is importing a specific file based on some variables
input into some input boxes. The files will always have the same naming
scheme and the macro will import the correct file based on the code and
variables. My problem is that the code is opening a dialog box in order to
select the file. My code already selects the file but the user has to hit ok
in order to move on. I want to suppress this dialog box as the code should
be doing the selecting of the file. I've attempted turning the DisplayAlerts
to false and it hasn't worked to suppress the box. I've searched the board
for all of the keywords I can think of without any success. Thank you in
advance for your assistance. See code below.

Thank you,
Brian

Sheets("Sheet1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _
"TEXT;\\maninfs1\account\FINANCE\" & Year & "\" & Month &
"\Files\ShowProfit" & Year & "" & Month & ""
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(30, 8, 13, 16, 19, 17)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With