Excel Macro - Run open file dialog box if no file selected

Question

I have a Macro (below) that opens the dialog box to choose a file and opens it into the 'Invest' sheet, then runs a macro to breakdown the Invest file to selected data

However, If somebody press's cancel or closes the dialog box the macro to breakdown the Invest sheet still runs.

Can somebody help me stop this bug, So if no file is selected the breakdown macro won't run?

Sub Import()
  ' Imports file '
  Application.ScreenUpdating = False
  Dim WS As Worksheet, strFile As String
  Set WS = ActiveWorkbook.Sheets("Invest")
  strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file...")
  With WS.QueryTables.Add(Connection:="TEXT;" & strFile, _
      Destination:=WS.Range("A1"))
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      .TextFilePlatform = 850
      .TextFileStartRow = 1
      .TextFileParseType = xlDelimited
      .TextFileTextQualifier = xlTextQualifierDoubleQuote
      .TextFileConsecutiveDelimiter = False
      .TextFileTabDelimiter = True
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = True
      .TextFileSpaceDelimiter = False
      .TextFileTrailingMinusNumbers = True
      .Refresh BackgroundQuery:=False
  End With
  ' Imports file '
  '''''''
  'RUN MACRO CODE HERE
  '''''''
End Sub

http://pastebin.com/6zQr6ahi – Jacko058 Feb 17 '14 at 03:22 — Jacko058, Feb 17 '14 at 03:22

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

I think the error handling your looking for goes something like this:

Dim fn As String
fn = Application.GetOpenFilename("All Files,.", 1, "Select a file", , False)

If fn = "False" Then
    Exit Sub
End If

This comes from another Stackoverflow page here Excel VBA Open File (error handling)

edited May 23 '17 at 12:30

Community

1
1

answered Feb 17 '14 at 03:33

NickF

83
8

Thanks, Just struggling to add that to my code.. The macro e.g Call test - still runs =/ – Jacko058 Feb 17 '14 at 03:49
1

In your code the DialogBox sends back a string and sets the variable strFile to whatever the user entered. If cancel was pressed then I believe the string "False" is returned. Therefore all you should need is the line If strFile = "False" Then Exit Sub. This should follow the line where you set strFile. Hope this helps. – NickF Feb 17 '14 at 04:02

Excel Macro - Run open file dialog box if no file selected

1 Answers1