?
Solved

Excel VBA QueryTable Error Handling

Posted on 2009-12-29
6
Medium Priority
?
713 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:jrmorse
  • 3
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26138650
What is srcfilename and how is it initialised?
0
 

Author Comment

by:jrmorse
ID: 26138683
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26138860
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jrmorse
ID: 26138921
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 26138950
Just noticed something. Change line 13 to:


.TextFilePromptOnRefresh = False

Open in new window

0
 

Author Comment

by:jrmorse
ID: 26139004
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question