on error exit sub

itsme2

Board Regular
Joined
Jul 29, 2006
Messages
66
I am having two problems with this code.

The first is when the macro is executed the default file name is that of the workbook and I only want to do queries on html files so, if one would hit "save" with this .xls file in the filename field of the dialogue box the code simply executes then fails. How do I prevent the default workbook name or any file name other then the file extension .html from appearing in the dialogue box.

There is no graceful exit for cancel this query either. If the code is run and you decide to "cancel" the query instead of "save" from the dialogue box, the code continues to execute and then crashes. The .Refresh BackgroundQuery:=False is the culprit for this failure condition.

I have remarked out the second macro that runs once this code succesfully executes because, at this time an unsuccessful attempt will allow the second macro to run anyway. I have put the IF statement before and after the query as well as before the GetaAsFileNameSave and still no good

The second problem is my condition If statement regarding a filename of "" or nothing. The code just crashes and does not just simply exit the sub.

What am I doing wrong here?

Code:
Sub GetReportData()

    Worksheets.Add
    Dim FileName, SaveDir
    'save the current directory into a string
    SaveDir = CurDir
    'change the working directory here to the default location you expect your
    'html files to reside
    ChDir "C:\"
    FileName = Application.GetSaveAsFilename(, "HTML Files (*.HTML), *.HTML")
          
    ChDir SaveDir
    'restore the working directory back to where it was initially
     If FileName = " " Then 
        ActiveSheet.Delete
        Exit Sub
    End If


    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;file:///" & FileName, Destination:=Range("A1"))
        .Name = " "
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    "Application.Run "Finish the Code"
    
End Sub


HELP!!!!!!!!!
:eek:
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Just off the top of my head...

if you put " " instead of "" your saying you want a space instead of blank.

If you just want to make sure its not blank then use the following.
if it passes then it will jump to the continue.
if it faults it dies at exit sub or if the name is blank it deletes the active then exits.

If Filename = "" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Else
GoTo ContinueRunning:
End If

Exit Sub
ContinueRunning:




End Sub
 
Upvote 0
On Error Exit Sub

Changing this first FileName line to:

FileName = Application.GetSaveAsFilename(HTML, "HTML Files (*.HTML), *.HTML")

Cleared putting the default .xls in the dialogue box however,

Your code

Code:
If Filename = "" Then 
Application.DisplayAlerts = False 
ActiveSheet.Delete 
Application.DisplayAlerts = True 
Else 
GoTo ContinueRunning: 
End If 

Exit Sub 
ContinueRunning: 




End Sub

Still does not execute it simply is ignored and the second application runs.

I suppose what would be simplest is to use some form of onerror event so If the user decides to select "CANCEL" instead of "SAVE" the code gracefully exits the sub....

Any ideas on this? I have been googling on OnError and cannot seem to locate what I want.... Is there a condition that can be written to handle the "CANCEL" selection from this dialogue box?

It took me long enough to deduce the Finder.File application this other problem one of my users has pointed out to me.

:oops:
 
Upvote 0
On Error Exit Sub

Changing this first FileName line to:

FileName = Application.GetSaveAsFilename(HTML, "HTML Files (*.HTML), *.HTML")

Cleared putting the default .xls in the dialogue box however,

Your code

Code:
If Filename = "" Then 
Application.DisplayAlerts = False 
ActiveSheet.Delete 
Application.DisplayAlerts = True 
Else 
GoTo ContinueRunning: 
End If 

Exit Sub 
ContinueRunning: 




End Sub

Still does not execute it simply is ignored and the second application runs.

I suppose what would be simplest is to use some form of onerror event so If the user decides to select "CANCEL" instead of "SAVE" the code gracefully exits the sub....

Any ideas on this? I have been googling on OnError and cannot seem to locate what I want.... Is there a condition that can be written to handle the "CANCEL" selection from this dialogue box?

It took me long enough to deduce the Finder.File application this other problem one of my users has pointed out to me.

:oops:
 
Upvote 0
Why are you using GetSaveAsFilename?

Why not use GetOpenFileName?
Code:
Sub GetReportData()
Dim FileName, SaveDir

    Worksheets.Add
    'save the current directory into a string
    SaveDir = CurDir
    'change the working directory here to the default location you expect your
    'html files to reside
    ChDir "C:\"
    FileName = Application.GetOpenFilename("HTML Files (*.HTML), *.HTML")
         
    ChDir SaveDir
    'restore the working directory back to where it was initially
     If TypeName(FileName) = "Boolean" Then
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Exit Sub
    End If


    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;file:///" & FileName, Destination:=Range("A1"))
        .Name = " "
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    'Application.Run "Finish the Code"
   
End Sub
 
Upvote 0
Well thanks a bunch and after all that will work thanks... I was initially using Finder:File which illiminated alot of this but this works just dandy thank you
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top