hyperdreamz
New Member
- Joined
- Sep 30, 2004
- Messages
- 11
Hi All
I am trying to write a macro to delete all connections from a very large excel file
I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)
The issue is the 1000+ connections on this file make it next to impossible to operate
I cannot recreate the file
Need some help with a macro to delete all connections in all sheets in a given work book.
Sample :http://www.4shared.com/file/95922031/b7611f8f/Book1.html
Macro used :
Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myURL, _
Destination:=Range(ActiveCell.Address))
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.PreserveFormatting = False
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
'.WebTables = "9"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
'.Refresh BackgroundQuery:=False
End With
Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "#REF!") > 0 Then
nName.Delete
End If
Next nName
End Sub
URL's on sheet1:
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
I am trying to write a macro to delete all connections from a very large excel file
I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)
The issue is the 1000+ connections on this file make it next to impossible to operate
I cannot recreate the file
Need some help with a macro to delete all connections in all sheets in a given work book.
Sample :http://www.4shared.com/file/95922031/b7611f8f/Book1.html
Macro used :
Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myURL, _
Destination:=Range(ActiveCell.Address))
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.PreserveFormatting = False
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
'.WebTables = "9"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
'.Refresh BackgroundQuery:=False
End With
Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "#REF!") > 0 Then
nName.Delete
End If
Next nName
End Sub
URL's on sheet1:
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
Last edited: