Full Version: Browse to refresh linked Excel spreadsheet
UtterAccess Forums > Microsoft® Access > Access Forms
mjennings
I am looking for a method similar to a file browse feature.
I have a spreadsheet with named ranges embedded. I want to build a browse feature that allows the user to pick the path and file name where the named ranges live (the named ranges will always be the same). I can get to the point of picking the path and filename, then storing it in an unbound text control, but don't know how to go the extra step to refresh the links using the stored path.
Since the named range is at the end of the path to the xls (drive:\path\filename.xls\NamedRange), I suspect I will need to concatenate the constant named range names to the path. But am not aware of how to get Access to perform a refresh through code.
Help would be greatly appreciated.
Thx - Mark
freakazeud
Hi,
I'm not sure if you are having problems with initially linking to the file or actual refreshing of an existing table link. If the the initial linking is the issue then check the TransferSpreadsheet method. If the second then check this earlier discussion for code.
HTH
Good luck
mjennings
No the intial link will have been already established. And in the user's Excel sheet, the named range will always be the same. What will be variable is the location of the Excel spreadsheet.
want to initiate the same action as if a user called the Linked Table Manager and checked the 'Always prompt for new location'.
This makes me think that if code can be written to perform the equivalent of selecting the links to be refreshed, then call up the Select New Location dialog box that typically displays from the Linked Table Manager, then this would be the easiest code solution.
freakazeud
To call up the browse dialog for the user to select the new location check this API. Then use the code given in the earlier discussion to refresh the links.
HTH
Good luck
mjennings
Thanks.
I actually have started building a process using your suggestion of Tansfer Spreadsheet (I didn't realize it did 'Link' also). I store the path to a variable, then do a delete object (removing the old link), then transfer spreadsheet using the path name and range name.
It seems to work fine, but I need to put some checks in to make certain it finds the object before it deletes the object. Otherwise, the process gets disrupted if for some reason it doesn't find the object before it attempts to delete it.
freakazeud
Do a search on UA on how to check before deleting a table.
HTH
Good luck
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.