Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get external data by dynamical url - no update
Hi there,
i have a problem to update my external data. I read a URL from the Field B5 and then i try to connect to this URL and read my datas but Excel doesnt update this data when i change the URL. I guess this problem is related to the "import data" dialog in Excel, but i'm not sure. here is my code: 'Read the current URL Sheets("Sheet1").Select Dim zelle zelle = "B5" Dim wert wert = Range(zelle).value 'update sheet Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & wert & "" _ , Destination:=Range("A1")) .Name = "AlertUser" .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 = _ """Header_g_a134c740_e525_4177_bdd8_e384ed89782d_a lertstable""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With I hope that anyone can help me. Best Regards Julian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get external data by dynamical url - no update
no ideas?
drofluga schrieb: Hi there, i have a problem to update my external data. I read a URL from the Field B5 and then i try to connect to this URL and read my datas but Excel doesnt update this data when i change the URL. I guess this problem is related to the "import data" dialog in Excel, but i'm not sure. here is my code: 'Read the current URL Sheets("Sheet1").Select Dim zelle zelle = "B5" Dim wert wert = Range(zelle).value 'update sheet Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & wert & "" _ , Destination:=Range("A1")) .Name = "AlertUser" .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 = _ """Header_g_a134c740_e525_4177_bdd8_e384ed89782d_a lertstable""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With I hope that anyone can help me. Best Regards Julian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get external data by dynamical url - no update
can you not try an event code like
worksheet_change. so that if the target value is changed the corresponding url is opened. "drofluga" wrote in message ps.com... no ideas? drofluga schrieb: Hi there, i have a problem to update my external data. I read a URL from the Field B5 and then i try to connect to this URL and read my datas but Excel doesnt update this data when i change the URL. I guess this problem is related to the "import data" dialog in Excel, but i'm not sure. here is my code: 'Read the current URL Sheets("Sheet1").Select Dim zelle zelle = "B5" Dim wert wert = Range(zelle).value 'update sheet Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & wert & "" _ , Destination:=Range("A1")) .Name = "AlertUser" .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 = _ """Header_g_a134c740_e525_4177_bdd8_e384ed89782d_a lertstable""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With I hope that anyone can help me. Best Regards Julian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get external data by dynamical url - no update
Thanks for you idea, but the URL is always correct. Excel only doesnt
put the data into the sheet when i change the url. regards, R..VENKATARAMAN schrieb: can you not try an event code like worksheet_change. so that if the target value is changed the corresponding url is opened. "drofluga" wrote in message ps.com... no ideas? drofluga schrieb: Hi there, i have a problem to update my external data. I read a URL from the Field B5 and then i try to connect to this URL and read my datas but Excel doesnt update this data when i change the URL. I guess this problem is related to the "import data" dialog in Excel, but i'm not sure. here is my code: 'Read the current URL Sheets("Sheet1").Select Dim zelle zelle = "B5" Dim wert wert = Range(zelle).value 'update sheet Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & wert & "" _ , Destination:=Range("A1")) .Name = "AlertUser" .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 = _ """Header_g_a134c740_e525_4177_bdd8_e384ed89782d_a lertstable""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With I hope that anyone can help me. Best Regards Julian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Warning Message - I have No External Data in wrkbk | Excel Discussion (Misc queries) | |||
Update data from an external Excel spreadsheet | Excel Programming | |||
Update External Data source from Excel sheet | Excel Discussion (Misc queries) | |||
How to create a chart based on a 2 dim data range dynamical in 1 d | Charts and Charting in Excel | |||
Pivot table won't refresh after external data update on open | Excel Programming |