Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Web Queries
Hi,
Have come across a new problem, Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic web updates based on cell data see below Dim URL2Use As String URL2Use = "URL;" & Sheet1.Range("D1").Value With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _ Destination:=Range("A10")) .Name = "AutoLink" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .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 This works in a new workbook, but doesn't work when I add a identical page and macro to an existing workbook, Have had Runtime 1004 errors, invalid web address errors and background refresh errors, yet it works fine in a dedicated workbook?? Any thoughts |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Web Queries
Sometimes Sheet1 in the workbook is not actually named "Sheet1".
Try this: Replace the Sheet1 ref in the below code to the actual tab name: Change this: URL2Use = "URL;" & Sheet1.Range("D1").Value to something like this: URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value Does that help? *********** Regards, Ron "Alarmbloke" wrote: Hi, Have come across a new problem, Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic web updates based on cell data see below Dim URL2Use As String URL2Use = "URL;" & Sheet1.Range("D1").Value With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _ Destination:=Range("A10")) .Name = "AutoLink" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .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 This works in a new workbook, but doesn't work when I add a identical page and macro to an existing workbook, Have had Runtime 1004 errors, invalid web address errors and background refresh errors, yet it works fine in a dedicated workbook?? Any thoughts |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Corrected code typo
So as not to have sent you a sabotaged suggestion....
The replacement example code should have been: URL2Use = "URL;" & Sheets("mySheet").Range("D1").Value (Changed Sheet to Sheets) *********** Regards, Ron "Ron Coderre" wrote: Sometimes Sheet1 in the workbook is not actually named "Sheet1". Try this: Replace the Sheet1 ref in the below code to the actual tab name: Change this: URL2Use = "URL;" & Sheet1.Range("D1").Value to something like this: URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value Does that help? *********** Regards, Ron "Alarmbloke" wrote: Hi, Have come across a new problem, Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic web updates based on cell data see below Dim URL2Use As String URL2Use = "URL;" & Sheet1.Range("D1").Value With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _ Destination:=Range("A10")) .Name = "AutoLink" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .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 This works in a new workbook, but doesn't work when I add a identical page and macro to an existing workbook, Have had Runtime 1004 errors, invalid web address errors and background refresh errors, yet it works fine in a dedicated workbook?? Any thoughts |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Corrected code typo
Yes, the second one worked a treat thanks, the sheet was labelled as sheet 1
but was added half way through the project with the original sheet1 renamed a long time ago. Is there any way of telling the true sheet names?? Once again, hat off to you Sir, "Ron Coderre" wrote: So as not to have sent you a sabotaged suggestion.... The replacement example code should have been: URL2Use = "URL;" & Sheets("mySheet").Range("D1").Value (Changed Sheet to Sheets) *********** Regards, Ron "Ron Coderre" wrote: Sometimes Sheet1 in the workbook is not actually named "Sheet1". Try this: Replace the Sheet1 ref in the below code to the actual tab name: Change this: URL2Use = "URL;" & Sheet1.Range("D1").Value to something like this: URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value Does that help? *********** Regards, Ron "Alarmbloke" wrote: Hi, Have come across a new problem, Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic web updates based on cell data see below Dim URL2Use As String URL2Use = "URL;" & Sheet1.Range("D1").Value With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _ Destination:=Range("A10")) .Name = "AutoLink" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .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 This works in a new workbook, but doesn't work when I add a identical page and macro to an existing workbook, Have had Runtime 1004 errors, invalid web address errors and background refresh errors, yet it works fine in a dedicated workbook?? Any thoughts |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Corrected code typo
Strike that last request, just right click the sheet and 'View Code' Sheet
numbers then listed in VBA list !! Should have guessed that really !! Thanks again "Ron Coderre" wrote: So as not to have sent you a sabotaged suggestion.... The replacement example code should have been: URL2Use = "URL;" & Sheets("mySheet").Range("D1").Value (Changed Sheet to Sheets) *********** Regards, Ron "Ron Coderre" wrote: Sometimes Sheet1 in the workbook is not actually named "Sheet1". Try this: Replace the Sheet1 ref in the below code to the actual tab name: Change this: URL2Use = "URL;" & Sheet1.Range("D1").Value to something like this: URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value Does that help? *********** Regards, Ron "Alarmbloke" wrote: Hi, Have come across a new problem, Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic web updates based on cell data see below Dim URL2Use As String URL2Use = "URL;" & Sheet1.Range("D1").Value With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _ Destination:=Range("A10")) .Name = "AutoLink" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .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 This works in a new workbook, but doesn't work when I add a identical page and macro to an existing workbook, Have had Runtime 1004 errors, invalid web address errors and background refresh errors, yet it works fine in a dedicated workbook?? Any thoughts |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to do automatic Web Queries?? | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions | |||
How can I find automatic links in an Excel-sheet? | Excel Discussion (Misc queries) | |||
automatic query refresh | Excel Discussion (Misc queries) | |||
automatic query refresh | Excel Discussion (Misc queries) |