Importing Data

[ddrew]

I have a form with a subform.

In the footer of the subform I have a combobox that get the filenames of excel spreadsheets from a folder.

I have a button (no code on it yet) that I want to press and import that spreadsheet into the table for the subform and associate it with the main form.

How should I go about this please?

 

[Frothingslosh]

It sounds like you should start by looking into DoCmd.TransferSpreadsheet.

 

[ddrew]

OK Ive had a look and I now have some code in place

    Dim sSource As String    'The name of the file to be copied

    sSource = "c:\GPandDetectionDogTrainingLogBackUpsandReports\"

        'Copies the files from Excel
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Detection", sSource, True, "tbl_Detection"

        MsgBox "Record has been Imported"

But it throws an error on the DoCmd
Run-time error '3051'
The Microsoft Access database engine cannot open or write to the file 'c:\GPandDetectionDogTrainingLogBackUpsandReports\'. It is already opened exclusivley by another user, or you need permission to view and write its data.

 

[Frothingslosh]

Have you confirmed no one has that particular spreadsheet open when you were doing this? That will definitely keep TransferSpreadsheet from working.

If you need, I have a code snippet floating around here somewhere to check for that kind of thing.

 

[ddrew]

Have you confirmed no one has that particular spreadsheet open when you were doing this? That will definitely keep TransferSpreadsheet from working.

If you need, I have a code snippet floating around here somewhere to check for that kind of thing.

That was my first thought, but I dont have anything open!

 

[Frothingslosh]

Okay, and the spreadsheet can be opened and used by anyone, right? (Assuming it weren't on your C: drive, anyways.)

Which version of Excel was it made with?

 

[ddrew]

Okay, and the spreadsheet can be opened and used by anyone, right? (Assuming it weren't on your C: drive, anyways.)

Which version of Excel was it made with?

Yup and it was made with Excel 2010

 

[Frothingslosh]

it was made with Excel 2010

There's your problem. You need to use acSpreadsheetTypeExcel14. Excel9 is Excel 2000.

 

[ddrew]

There's your problem. You need to use acSpreadsheetTypeExcel14. Excel9 is Excel 2000.

I have an Export button and this is the line of code that I use to export a record

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", strBackUpDogNameFolder & "\BackUpTraining.xls", True

So logic says that I should be using:

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Detection", sSource, True, "tbl_Detection"

for the import, or am I missing something

 

[Frothingslosh]

the argument where you're using acSpreadsheetTypeExcel9 tells Access what version of Excel the spreadsheet will be. It's easy with an export - it just tells Access to export the file in Excel 2000 format. Exports will generally go without a hitch unless you're trying to, say, export in Excel 2013 format from Access 2000.

On an IMPORT, you're telling it what version of Excel the version was saved in, which in turn tells it how to read the file. In this case, the end result is much like trying to read Old English when you only know how to read Modern English - it just can't be done. When importing, you need to make sure the specified format matches the file. I've not tried to see if an Excel10 (Excel 2003) specification will allow you to read an Excel 2000 file, but I guarantee you that an Excel9 (Excel 2000) specification will prevent accessing an Excel 2010 file - they are saved in completely different formats.

 

[ddrew]

Well I've fixed part of the problem.

It was the string, I was only looking for the folder and not the file

sSource = "c:\GPandDetectionDogTrainingLogBackUpsandReports\"

I created an unbound txtbox to hold the name of the file from the cbobox (txt_ImportName). So the code now reads

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Detection", sSource & txt_ImportName, True, "tbl_Detection"

 

[Frothingslosh]

Crap, I should have seen that, sorry.

So is it importing?