PDA

View Full Version : Solved: Import HTML Query Named range issue



xluser2007
01-21-2009, 05:52 PM
Hi All,

I have the following code, which works well to import a table from a HTML file I have created previously.

The only problem is that everytime it imports the query (and I may have to import multiple times if the HTML file is refreshed), it creates a named range for the table in Excel like "External_data_X", where the X is a number.

My code is as below:

Option Explicit

Sub Import_HTML_Table(strSourceHTMLfile As String, rngDestRange As Range)

With rngDestRange.Parent.QueryTables.Add(Connection:= _
"FINDER;file:" & strSourceHTMLfile _
, Destination:=rngDestRange)


.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SaveData = True
.RefreshStyle = xlOverwriteCells ' This is important
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With

End Sub

I would like to know how to do the import without creating the named range every time the import occurs.

If anyone could please explain how to go about this, it would be appreciated.

regards

xluser2007
01-21-2009, 07:27 PM
Hi All,

I tried the using the NAME property of the querytable following:

Sub Import_HTML_Table(strSourceHTMLfile As String, rngDestRange As Range)

With rngDestRange.Parent.QueryTables.Add(Connection:= _
"FINDER;file:" & strSourceHTMLfile _
, Destination:=rngDestRange)

.Name = vbNullString
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SaveData = True
.RefreshStyle = xlOverwriteCells ' This is important
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
' .WebSelectionType = xlSpecifiedTables
' .WebTables = "2"
.WebFormatting = xlWebFormattingNone ' xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With

End Sub
But this didin't work.

Could anyone please help with this?

xluser2007
01-22-2009, 01:38 AM
Any help with this is sincerely appreciated VBAXers.

regards,

Bob Phillips
01-22-2009, 03:07 AM
Why, what problem is the name causing?

xluser2007
01-22-2009, 03:31 AM
Why, what problem is the name causing?
Hi Bob,

Basically what happnes is that very time I rerun the code for a particular HTML table IMPORT, it produces a local named range for that table, of the form:

[worksheet name]!ExternalData_67 for example.

As the HTML files are often refreshed with new data, the next time the macro is run, it adds another named range [worksheet name]!ExternalData_68 for example for the same Querytable.

Given that there are 4 tables and that there are multiple imports happening, the number of local these unused local named ranges grows considerably.

As there is no purpose served by these named ranges they only confuse any other user of the spreadsheet and casue too much clutter compared to the useful names in the workbook.

As such, I was hoping to not have the query table imported as a named range unless required.

I searched the name property of the Querytable but couldn't find out how to remove it.

As such, I require assistance with this issue.

regards,

Bob Phillips
01-22-2009, 03:43 AM
I don't think that you can stp it, it is Excel system names.

How about just deleteing any before you run the query?

xluser2007
01-22-2009, 05:16 AM
I don't think that you can stp it, it is Excel system names.

How about just deleteing any before you run the query?
Bob, a macro to delete the nelwy created ununsed named ranges this was the workaround I was thinking of earlier today. I just thought there may be a simpler way using the Name property of the QueryTable. But if you feel that they are in built, I will go with your recommendation.

I have started trying to do a generic code to run before and after the import as follows, but am having trouble doing a WILDCARD step in the name delete:

Option Explicit

Sub DeleteQueryTableNames()

' Need to add in appropriate error handling for the Code

Dim oneName as Name

For each oneName in Thisworkbook.names

' need to add wildcard to delet any local named range of the form
' worksheet_name!ExternalData_XX then OneName.delete
' The import HTML apparanetly only creates LOCAL named ranges, but we should try and
' GLOBAL named ranges of the form ExternalData_XX

If oneName.name Like 'worksheet_name!ExternalData_XX then OneName.delete

Next oneName

End Sub
Do you feel the above is on the right track, and if so could you please help amend the code.

kind regards and thanks for your help.

Bob Phillips
01-22-2009, 06:17 AM
The Name property of the query referes to the query name, not a system name that Excel creates to tell it where the data is located, so it is of no use to you.

Here is the code (untested her)



Option Explicit

Sub DeleteQueryTableNames()

' Need to add in appropriate error handling for the Code

Dim oneName As Name

For Each oneName In ThisWorkbook.Names

If oneName.Name Like "*ExternalData_*" Then oneName.Delete
Next oneName

End Sub

xluser2007
01-22-2009, 03:32 PM
The Name property of the query referes to the query name, not a system name that Excel creates to tell it where the data is located, so it is of no use to you.

Thanks for the clarification Bob, makes sense.


Here is the code (untested her)



Option Explicit

Sub DeleteQueryTableNames()

' Need to add in appropriate error handling for the Code

Dim oneName As Name

For Each oneName In ThisWorkbook.Names

If oneName.Name Like "*ExternalData_*" Then oneName.Delete
Next oneName

End Sub

Works a treat, thanks for your help! :)

regards,