Excel VBA QueryTable Error Handling

Hello Experts -

I am refreshing a querytable in VBA.  When prompted for a file, if the user presses Cancel instead of Open the macro continues to run and errors out.  Can someone tell me how to stop the macro if the user presses Cancel?

Thanks in advance,
Jess
'Prompt user for file to process
With ActiveSheet.QueryTables(1)
    .Connection = "TEXT;" & srcfileName
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .TextFileCommaDelimiter = True
    .TextFileStartRow = 6
    .PreserveFormatting = True
    .AdjustColumnWidth = False
    .RefreshOnFileOpen = False
    .TextFileTrailingMinusNumbers = True
    .SaveData = True 
    .TextFilePromptOnRefresh = True 
    .Refresh
End With

Open in new window

jrmorseAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Just noticed something. Change line 13 to:


.TextFilePromptOnRefresh = False

Open in new window

0
 
Rory ArchibaldCommented:
What is srcfilename and how is it initialised?
0
 
jrmorseAuthor Commented:
srcfilename used to be filename and it was never initialized.  I changed it to this variable while trying to catch if the user hit cancel.  This code is right above the piece I already posted:


Dim srcfileName
srcfileName = Application.GetOpenFilename("Other Workbook (*.*),*.*")
If srcfileName = "False" Then
  Exit Sub
End If

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rory ArchibaldCommented:
Tha code should work to exit, though it would be better to use:


Dim srcfileName
srcfileName = Application.GetOpenFilename("Other Workbook (*.*),*.*")
If srcfileName = False Then
  Exit Sub
End If

Open in new window

0
 
jrmorseAuthor Commented:
I forgot to mention - the issue with this method (srcfilename) is that the user is prompted twice for the file, and the second time they are prompted they can still cancel and break the macro.
0
 
jrmorseAuthor Commented:
Thanks, final code is below for others to reuse in future.

'Error handling while user prompted for filename; if cancelled macro is stopped
Dim srcfileName
srcfileName = Application.GetOpenFilename("Other Workbook (*.*),*.*")
If srcfileName = "False" Then
  Exit Sub
End If

'Refresh the querytable.  If receive Error 13 install Office Service Pack 3
With ActiveSheet.QueryTables(1)
    .Connection = "TEXT;" & srcfileName
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .TextFileCommaDelimiter = True
    .TextFileStartRow = 6 'Improvement: Make this variable
    .PreserveFormatting = True
    .AdjustColumnWidth = False 'Let user adjust
    .RefreshOnFileOpen = False
    .TextFileTrailingMinusNumbers = True 'negative numbers keep negative sign
    .SaveData = True 'save data within the workbook
    .TextFilePromptOnRefresh = False 'Set to false or user is prompted twice
    .Refresh
End With

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.