Import spec solution for xls?

[chris-uk-lad]

Hi all,

I have an xls file i want to import into a table in my access database, but i need all imported data types to be text and no primary key included. I know you can achieve this with a txt/csv document but need to ensure this with this xls.

atm im using the simple command to import in the document

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel3, "NewData", FilePath & "update.xls", True

I hear you can do this then run through a query but ive never ran a query via code before.

Many Thanks

 

[DCrake]

Couple of steps to do in order to acheive this.

First create a table that has the matching fields as the incoming spreadsheet but make the fields text fields.

Next link the spreadsheet to the database in the normal xls fashion.

Finally create an append query to append the linked spreadsheet to the existing access table. Make sure that you map the fields correctly. This way your data will be imported into text type fields.

David

 

[chris-uk-lad]

Thanks for the tips! Ill need to run this query every time a button is pressed though, what might the command be for this?

 

[DCrake]

On the on click of your command button

DoCmd.SetWarnings False
DoCmd.OpenQuery YourQueryName, acNormal, acEdit
DoCmd.SetWarnings True

You may find that you need to delete the contents of the existing table so you don't get duplicate records.

 

[chris-uk-lad]

That worked fine, thanks

Just as a side question, is there a way to compare the initial spreadsheet with a another tables column headings? so i could pop up an error message when there are'nt matching columns before running the query?

 

[DCrake]

What error are you attempting to trap? If your existing table has the correct number/type of fields and the linked spreadsheet is effectively read only then why the need for comaprison?

David

 

[chris-uk-lad]

The document im linked to in order to change the data types (update.xls) is updated often. Aslong as the names correct then the link will work. If ever an extra column is found though, id like an error alert.

If this is not possible with the current setup its ok, would just be a nice addition.

 

[DCrake]

Ok,

So we know how many columns we are expecting (same as the number of fields in the target table.

So if we count the number of columns in the linked spreasheet and find it to be different then we can throw a msgbox up

Then on the onclick of the command button place the following

Dim rCnt As Integer
Dim nFields As Integer
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("NameOfYourExcelSpreadsheet")
rCnt = Rst.Fields.Count
Rst.Close
Set Rst = Nothing

nFields = nnn 'Number of expected columns - see above

If rCnt < nFields Then
   Msgbox "Linked Spreadsheet does not match the existing mapping.",vbExclamation+vbOkOnly,"Mapping Error"
   Exit Sub
ElseIf rCnt > nFields Then
   Msgbox "Linked Spreadsheet contains additional fields.",vbExclamation+vbOkOnly,"Mapping Error"
   Exit Sub
End If

 

[gemma-the-husky]

i do this, dc

i have some apps that need certain columns to be included in the imported spreadsheet. so i import to a table, then in order to make sure they have selected the correct file, then check that all the columns that should be there, are there.

you could also open the spreadsheet and check the entries in row 1, if you intend to manipulate the spreadsheet directly