Defining fields using TransferSpreadsheet

[kcarpy]

Hi.

I'm wondering if there is a way to define the settings for each column to be imported when importing from Excel using TransferSpreadsheet.

As an example, I am trying to import a four-column spreadsheet from excel in which the first column contains mostly numeric but a few text entries. I want this to import as text, but it seems to default to long integer. Is there a way to force it to import a particular column as text using code?

Thanks.

 

[KenHigg]

The reason this happens is that MS Access looks at the top x number of rows and takes a best guess as to what the data type is. If you had a text in the top thre or four rows I'm guessing in would import correctly.

Anyway, my suggestion is to put the spreadsheet in a folder and attach to it and set up the columns as needed. Then when you have a new spreadsheet data replace the old .xls file with the new one and import the new data using code...

Hopes this makes sense.

 

[kcarpy]

Thanks.

My issue is that it's a bunch of spreadsheets, all with the data set up the same way, and I'll need to do it repeatedly, so I wanted to just write code to do it.

I think what I'll do is simply create the import tables first, set the field properties, and then import into those tables. As far as I can tell, there is no way to set the properties while doing the import.

 

[dkinley]

Thanks.
I think what I'll do is simply create the import tables first, set the field properties, and then import into those tables.

Only way I could get it to work ... and in that order. If you find something different though, I know I'd be interested in hearing.

-dK

 

[KenHigg]

Another option is to access the data direct in the spreadsheet. From within Access you can open a spreadsheet and work with it just as though you were writing vba code in Excel. This sounds combersome at first but if you use a few loops, etc. it's really the ultimate in flexibility.

 

[DCrake]

Simple Software Solutions

Nobody has seemed to mention import specifications, why not create an import spec and use that everytime you import a spreadsheet.

CodeMaster: