[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Linking table to shared network folder

I am having trouble linking to an excel spreadsheet(or even importing it) from a shared network folder on our coporate network. I have tried two different ways. First I mapped a drive for the folder containing the spreadsheet, then I tried just using the UNC path to the file, I get the same error both ways. The error basically says access cannot find the path to the file, but it adds a pound sign(#) to the end of the filename instead of the excel extension(.xls) so I get an error like this:

using a UNC path I get excel cannot find the file \\networkshare\somefile# the actual path is \\networkshare\somefile.xls
I get the same error using a mapped drive" excel cannot find the file G:/somefile#

It is obvious that access can see the file because I can navigate to it wioth no problem but it removes the extension and adds "#"

Any ideas?
0
ashvillerob
Asked:
ashvillerob
  • 4
  • 3
  • 2
1 Solution
 
ashvillerobAuthor Commented:
Correction to above, access adds a dollar sign($) to the file name not a pound sign(#)
0
 
Steve BinkCommented:
Post your code, please.
0
 
ashvillerobAuthor Commented:
code?

I am simply using  File->GetExternalData->Import
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
charlietouCommented:
What are you trying to do, set a hyperlink to the file from within a field in a table?
Or are you just trying to incorporate the spreadsheet as a new table within your database?

If the second, then, starting from the main database window in the tables view,
choose File->Get External Data->Import, navigate to your network drive, and in the dropdown box at the bottom of the window, choose .xls files.  You should see a list of available files.  Once you choose the one you're looking for, Access will guide you through the Import Spreadsheet Wizard, and you should be home free.

Hope this helps,

Charlie T.
0
 
ashvillerobAuthor Commented:
Thanks but I am very familiar with linking a table to an excel spreadsheet or importing an excel spreadsheet into the database as a new table.

When the excel spreadsheet is stored on the local machine I have no problems, this problem is just happening when I try to import an excel spreadsheet into access from either a mapped drive or a network shared folder. I am able to choose File->Get External Data->Import, navigate to your network drive and select the spreadsheet, but once I hit ok, Access sits there a secound then complains that it cannot find the file and in the error it shows the file path and filename, but it adds a $ to the end of the file name.

For example the path is \\somesharedfolder\excelspreadsheet.xls

Access gives this error when trying to import the file:

Access cannot find the file \\somesharedfolder\excelspreadsheet$

As you can see Access removes the file extensiona dn adds a $ sign wwhen reporting the error.
0
 
charlietouCommented:
I don't get it.

I just tried it on my office network, navigated to a mapped drive, chose the file, and was done.

Stupid question, but what happens when you double-click the file?

Charlie T.
0
 
Steve BinkCommented:
Where exactly is it failing?  You said you can browse to and select the file...does the wizard stop there or do other prompts appear?  Also, have you checked the Excel file for corruption or weird data, or perhaps high-bit characters in the filename/worksheet name?  What version of Access are you using?  Excel?

Excel files are referenced as [filename]$[sheetname]![range], so it sounds like Access is taking the filename, adding the "$", then trying to append the sheet name, which it can't find.
0
 
ashvillerobAuthor Commented:
I gave up on Access for the import portion, now I use MySQL to import the excel spreadsheet and then export form MySQL into Access. I have never had a problem importing excel spreadsheets with Acess, so it has to be something related to fact that these spreadsheet are on a mapped drive. MySQL pulls them in with no problem at all.

Also to answer your questions I use Access 2003, the excel file is in excel 4.0 format.

When I choose File->Get External Data->Import, I can navigate to the mapped drive, select (excel file .xls) format in the dropdown menu, see all the excel files but after I select the excel file I want to import and hit enter, I get the error, that the file cannot be found. Frustrated me to the point of reverting to MySQL.
0
 
Steve BinkCommented:
My best guess is Access 2003 has something to do with the problem...I've heard about quite a few bugs in the new version and do not use it regularly myself (2002 at the office).  Did you try more than one file, or does it do with any file you try to import from a network drive?  What about non-Excel files?  I went to the MSKB and search on several different phrases, but all the results holding any promise regarded the ISAM entries in the registry, which I do not think is pertinent to your problem.

On the bright side, at least you found a way around it.  I'm sorry we could not be of more help.  If you'd like to continue experimenting with this to narrow down the problem, I'd be happy to sit through it with you.  Otherwise, you can post a request to close this question and refund your points at:

http://www.experts-exchange.com/Community_Support

Good luck with the rest of your project!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now