vba Excel 宏 - 如果未选择文件,则运行打开文件对话框

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/21820145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 01:56:19  来源:igfitidea点击:

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

excelexcel-vbadialogvba

提问by Hymano058

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

采纳答案by NickF

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)

这来自此处的另一个 Stackoverflow 页面 Excel VBA 打开文件(错误处理)