UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Web External Links Not Refreshing, Any Version    
 
   
M2C
post Jan 14 2020, 12:10 AM
Post#1



Posts: 669
Joined: 7-April 08
From: Dubai


Hi

I have been using the .xlsm attachment without issue in the UK for several years but have found that when I travel and go onto a different WIFI the file fails to refresh. I use the xlsm to populate tables in an Access db

There are 10 connections to get
https://www.x-rates.com/table/?from=AED&amount=1
https://www.x-rates.com/table/?from=USD&amount=1
https://www.x-rates.com/table/?from=GBP&amount=1
https://www.x-rates.com/table/?from=NZD&amount=1
https://www.x-rates.com/table/?from=AUD&amount=1
https://www.x-rates.com/table/?from=CHF&amount=1
https://www.x-rates.com/table/?from=ZAR&amount=1
https://www.x-rates.com/table/?from=PLN&amount=1
https://www.x-rates.com/table/?from=INR&amount=1
https://www.x-rates.com/table/?from=EUR&amount=1

The file has been refreshing on opening the file but now that I am in a different country the file no longer refreshes
I also notice that the Edit Links on the Data>Queries and Connections tab is greyed out.

I have tried to create a brand new file with the links above without success, any ideas what my issues are?

Thanks
Chris
This post has been edited by M2C: Jan 14 2020, 12:11 AM
Attached File(s)
Attached File  Currency___Copy.zip ( 58.89K )Number of downloads: 1
 

--------------------
Chris
Go to the top of the page
 
GroverParkGeorge
post Jan 14 2020, 07:57 PM
Post#2


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


I created a new worksheet in your xlsm using one of the links you posted. I named the new sheet "Euro". It works for me now, although the 10 existing connections do not. They all fail with the error that the column name does not exist. That indicates to me that the connection is occuring, but there's an error in the name of the columns.

See if this works for you. If it does, a possible solution would be to replace the existing sheets.

Attached File  Currency___Copy_Revised.zip ( 64.71K )Number of downloads: 1

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
M2C
post Jan 15 2020, 03:00 AM
Post#3



Posts: 669
Joined: 7-April 08
From: Dubai


Many thanks for taking a look

I have managed to add another sheet but cannot add more than 2 sheets. Also the Edit Links is greyed out so have no idea how to resolve. Any more suggestions?

Thanks
Chris
Attached File(s)
Attached File  Currency___Copy_Revised.zip ( 70.85K )Number of downloads: 0
 

--------------------
Chris
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2020, 08:20 AM
Post#4


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


I guess a bit more information might be helpful for me.

Did you add 2 sheets that now DO update as needed?

What error do you receive when you try to add more than 2 sheets.

What's stopping you from doing this in a new workbook, if anything?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
M2C
post Jan 15 2020, 01:41 PM
Post#5



Posts: 669
Joined: 7-April 08
From: Dubai


HI

The 2 sheets that I added work fine
The other sheets do not update and kick out an error with a msg for each sheet as attached
Creating a brand new workbook does not let me get passed creating 2 worksheets

This is happening because I have changed country from UK to UAE and there is something very weird going on. I have had this in the past twice before but always managed to recreate the workbook from scratch when on a new wifi network.

It is most bizarre

Any ideas?

Thanks
Chris
Attached File(s)
Attached File  Eur_Error.JPG ( 16.15K )Number of downloads: 0
 

--------------------
Chris
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2020, 02:10 PM
Post#6


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


I was able to verify that the existing links in the workbook fail. I was able to add several new worksheets using your links.

I don't know what the problem is.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
M2C
post Jan 15 2020, 03:19 PM
Post#7



Posts: 669
Joined: 7-April 08
From: Dubai


I really appreciate your help but I can only add two sheets when the third fails. I don't know what else to suggest as I do exactly the same routine and as you can see from the screenshot the Edit Links is always greyed out
Attached File(s)
Attached File  Error.JPG ( 212.14K )Number of downloads: 2
 

--------------------
Chris
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2020, 03:34 PM
Post#8


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


I am not knowledgeable enough about Excel to say more.

However, in your screen shot, I wonder what would happen if you select "Table 1" instead of Table 1 (2)"

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
M2C
post Jan 18 2020, 01:22 PM
Post#9



Posts: 669
Joined: 7-April 08
From: Dubai


OK so I have an answer to my problem but I don't understand why. It appears that when I get asked for the URL I need to leave off the https:// and only include the www

So this works
www.x-rates.com/table/?from=NZD&amount=1

And this doesn't
https://www.x-rates.com/table/?from=NZD&amount=1

When I use the www a window pops up which is too fast to read when I confirm the URL during the Get External Data from Web

Phew!

--------------------
Chris
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2020, 01:36 PM
Post#10


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


congratulations on solving the puzzle.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 07:34 PM