Link to home
Start Free TrialLog in
Avatar of c9k9h
c9k9h

asked on

Import a spreadsheet from Excel 2003 to Access 2003

I found some help on this by doing a search before sending in this question.  It seems pretty straightforward, however, I'm getting an error message when I attempt to execute.  Here is the docmd line that I have in the 'On Click' event of a command button on a form:

Private Sub cmdImportPCS_Click()
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PCS_Import", "N:\cs\mis\Metrics Database\Commission Services Database.xls", True, "A:F"
End Sub

Here is the error message: 'Run-time error 3275':  Unexpected error from external database driver (24).  I also found something on this that nico posted, but the link is no longer working.

Can an expert help me resolve this?  Thanks!
Avatar of jerryb30
jerryb30
Flag of United States of America image

Can you do a manual import of the same spreadsheet?
Avatar of c9k9h
c9k9h

ASKER

I have to run to a meeting, but will check it out when I get back.  Thanks!
Have you considered just linking the spreadsheet to Access?  You can then work with it like you would any other table.  
I tried it and I can’t break it using 2003 or 2002.
Can you check your DAO references?
Avatar of c9k9h

ASKER

I checked the DAO references and the 'Microsoft DAO 3.6 Object Library' is selected.  
Avatar of c9k9h

ASKER

Oh, and yes, I can do a manual import and it works fine.  
If you don't want to link the sheet then try using an UNC path instead of a Drive mapping:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PCS_Import", "\\yourserver\sharename\Metrics Database\Commission Services Database.xls", True, "A:F"

It depends on how the drive N mapping was done but you'll have to use the share point name.
Avatar of c9k9h

ASKER

This is what my 'n' drive is:  data on 'Chattanooga DFS server (cha1dfs1)'.  I've tried several combination of how to put it in code and I keep getting the same message (with the exception of whatever it is I enter in the 'cha1dfs1' area):

Run-time error ‘3011’:
The Microsoft Jet database engine could not find the object ‘C:\Documents and Settings\c9k9h\My Documents\cha1dfs1:\cs\mis ……….  Make sure the object exists and that you spell its name and the path name correctly.

And I thought I was missing something simple!  
start with \\cha1dfs1\ in windows explorer and see if it finds the server if you only have permisions to a share you may need the full path "\\cha1dfs1:\cs\mis ………. " The path to the file in explorer NOT using "N:\" is what you want.
what raymond said....From your description of drive N, it sounds like maybe:

\\cha1dfs1\Data\cs\mis\Metrics Database\Commission Services Database.xls

But go ahead and use explorer to find the right path starting with the server name
Avatar of c9k9h

ASKER

I seem to be getting closer, and at this point I think I need to get some internal help to find out what's going on.  Like one of you mentioned, it may be a 'read-only' thing.  I work on the file and update it, so I'm not sure what's up.  Here's the message I get now:

Run-time error ‘2220’:
Microsoft Office Access can’t open the file ‘\\cha1dfs1\cs\mis\Metrics Database\Commission Services Database.xls’.

At least it can find it now!
It does sound like you are on the right track.  I agree that you should get your IT guys on this issue.  It may be a file permissions issue as well.
SOLUTION
Avatar of RaymondSteers
RaymondSteers
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of c9k9h

ASKER

I've decided to use a Monarch project to handle what I need, and a batch file will load the data into Access.  Interestingly, the prj file has no problem opening and reading the file on the N drive.  Must be the drivers or something.  Anyhow, I split the points between RaymondSteers and Arji since you both helped me to try and resolve.  I appreciate it!  Thanks!
Monarch project?  what's that?  Have you tried just linking the spreadsheet to Access?
Avatar of c9k9h

ASKER

Monarch is pretty cool. (www.datawatch.com - Monarch V8 is in the spotlight section.) It takes reports and puts data into columns and rows.

I don't want to link because the data in the 40+ excel spreadsheets will change each month and the new data has to be appended to the existing data in Access.  Hmmmm, although that will be a problem in Monarch also, since an append is not available though code in the version I have.  You have given me something to think about.  I agree it is better to use as few applications as possible.  Hmmmm.  Thanks!
Appending data from a linked spreadsheet is pretty much the same as doing it from a linked table.  You just run and Append query first and then a Delete query to delete the appended data from the spreadsheet to get it ready for the next batch.  Make backups of your tables and spreadsheets and give it a try.  It might make your life a little simpler.  You just want to be sure your data types match correctly from  your spreadsheet to your table.
Avatar of c9k9h

ASKER

Thanks, Arji!