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?
HELP!!!!!!!!!
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!!!!!!!!!