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

pastebin.com/6zQr6ahi

Jacko058
– Jacko058

2014-02-17 03:22:32 +00:00
Commented Feb 17, 2014 at 3:22 — Jacko058
– Jacko058, Commented Feb 17, 2014 at 3:22

Community · Accepted Answer · 2017-05-23 12:30:56Z

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, 2017 at 12:30

CommunityBot

11 silver badge

answered Feb 17, 2014 at 3:33

NickF

839 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jacko058 Over a year ago

Thanks, Just struggling to add that to my code.. The macro e.g Call test - still runs =/

NickF Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related