Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to drive
Is there a way to import data to excel, from MS Access without haveing the
link referance a drive letter? I would like the spreadsheet to referance only the drive name. This way multiple users can update the excel file regardless of what drive letter they have the shared drive containing the DB mapped to. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to drive
Have you tried the UNC name? It should work the same.
"Cyhill" wrote in message ... Is there a way to import data to excel, from MS Access without haveing the link referance a drive letter? I would like the spreadsheet to referance only the drive name. This way multiple users can update the excel file regardless of what drive letter they have the shared drive containing the DB mapped to. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to drive
Hi Cyhill,
I'm presuming you're using Data~Import External Data~Import Data right-click on your imported table and select 'Edit Query'. In the connection text box, find where it says Source=C:\folder name\... (or whichever drive) and change this to the server name. You can find your server name by looking in Explorer - it will appear looking something like: common on 'server-name' (J:) in this case you would change the Source=C:\folder name\... to //server-name\common\folder name\... Remember the first two slashes are forward slashes rather than back slashed. Hope this works - worked for me :) (can you rate the post if it was helpful please - thanks). "Cyhill" wrote: Is there a way to import data to excel, from MS Access without haveing the link referance a drive letter? I would like the spreadsheet to referance only the drive name. This way multiple users can update the excel file regardless of what drive letter they have the shared drive containing the DB mapped to. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to dr
Darren - Thanks for the quick reply!!
However, I must be missing something. When I select 'Edit Query', I do not see the option you mention beow. The query wizard takes me to a 'Choose Columns' prompt. I'm running Office 2003...in case that makes a difference. "Darren Bartrup" wrote: Hi Cyhill, I'm presuming you're using Data~Import External Data~Import Data right-click on your imported table and select 'Edit Query'. In the connection text box, find where it says Source=C:\folder name\... (or whichever drive) and change this to the server name. You can find your server name by looking in Explorer - it will appear looking something like: common on 'server-name' (J:) in this case you would change the Source=C:\folder name\... to //server-name\common\folder name\... Remember the first two slashes are forward slashes rather than back slashed. Hope this works - worked for me :) (can you rate the post if it was helpful please - thanks). "Cyhill" wrote: Is there a way to import data to excel, from MS Access without haveing the link referance a drive letter? I would like the spreadsheet to referance only the drive name. This way multiple users can update the excel file regardless of what drive letter they have the shared drive containing the DB mapped to. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to dr
I'm using the following method:
Data Import External Data New DB Query... and then following the prompts to pinpoint the DB I wish to link to. At which point , (option), would I enter the UNC Name? Or is there a whole other process for importing data from access to excel using the UNC Name? Thanks, "-" wrote: Have you tried the UNC name? It should work the same. "Cyhill" wrote in message ... Is there a way to import data to excel, from MS Access without haveing the link referance a drive letter? I would like the spreadsheet to referance only the drive name. This way multiple users can update the excel file regardless of what drive letter they have the shared drive containing the DB mapped to. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to dr
OK, in Excel you select
Data Import External Data Import Data... This will bring up an open file dialogue box called 'Select Data Source' The 'Look In' combo box will be set to 'My Data Sources' and it will include things like '+Connect to New Data Source.odc' After you've navigated to your database you'll get a 'Select Table' dialogue box listing all the tables and queries in your database. After you've selected a table and pressed OK you'll get an 'Import Data' dialog box which asks where you want to import the data to (default cell $A$1 on the existing worksheet). One of the buttons at the bottom of this form says 'Edit Query...'. Pressing this brings up the 'Edit OLE DB Query' dialogue box which shows your connection parameters - looking something like: Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=H:\DATA\ACCESS\Database name.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False On the second line where it says H:\ change that to the server name as I described in the first post. If you right-click on an imported table it will give an option to 'Edit Query...' which brings up the 'Edit OLE DB Query'. If you're using Data Import External Data New Database Query (this is the way you've put your using in your post to - ) you'll get a dialogue box called 'Choose Data Source' and it will include things like '<New Data Source', '<Excel Files*' On your taskbar at the bottom of the screen there'll be a new icon called 'Microsoft Query' Once you've chosen a data source you'll get a 'Connecting to data source...' message come up and a 'Select Database' dialogue box. If you're using that method, then I'm afraid I can't help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import from MS Access - Lose the drive letter..link only to dr
Okay - Thanks for your tiome on this.
FYI: I tried VB (with no luck). However, MS Script Editor allowed me to update the path with the server name (removeing the drive letter). This seems to have worked on my test file. I will test further. Hopefully this will be what I'm looking for. Thanks for your time on this. "Darren Bartrup" wrote: OK, in Excel you select Data Import External Data Import Data... This will bring up an open file dialogue box called 'Select Data Source' The 'Look In' combo box will be set to 'My Data Sources' and it will include things like '+Connect to New Data Source.odc' After you've navigated to your database you'll get a 'Select Table' dialogue box listing all the tables and queries in your database. After you've selected a table and pressed OK you'll get an 'Import Data' dialog box which asks where you want to import the data to (default cell $A$1 on the existing worksheet). One of the buttons at the bottom of this form says 'Edit Query...'. Pressing this brings up the 'Edit OLE DB Query' dialogue box which shows your connection parameters - looking something like: Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=H:\DATA\ACCESS\Database name.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False On the second line where it says H:\ change that to the server name as I described in the first post. If you right-click on an imported table it will give an option to 'Edit Query...' which brings up the 'Edit OLE DB Query'. If you're using Data Import External Data New Database Query (this is the way you've put your using in your post to - ) you'll get a dialogue box called 'Choose Data Source' and it will include things like '<New Data Source', '<Excel Files*' On your taskbar at the bottom of the screen there'll be a new icon called 'Microsoft Query' Once you've chosen a data source you'll get a 'Connecting to data source...' message come up and a 'Select Database' dialogue box. If you're using that method, then I'm afraid I can't help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force linked files to link as a drive letter, not UNC name | Excel Discussion (Misc queries) | |||
Obtain drive letter assignment of CD/DVD drive? | Excel Discussion (Misc queries) | |||
I lose cell comments in Excel when moving a file via a USb drive | Excel Discussion (Misc queries) | |||
File in C drive is always trying to access Drive A | Excel Discussion (Misc queries) | |||
Link workbooks-C drive to network drive | Excel Worksheet Functions |