Use VBA to import column from excel into new column in Access using docmd.transferspreadsheet

[aemara]

Hi guys,



I am trying to write some VBA code that will read in 2 columns from an excel table, into one access table using the following code:

Sub getRange()

Dim xlObject As Object
Set xlObject = GetObject(Class:="Excel.Application")
    
Dim wrkbk As Workbook
Set wrkbk = xlObject.Workbooks("Book1.xlsx")

Dim wrs As Worksheet
Set wrs = wrkbk.Sheets("Sheet 1")

Dim rng As Range
Set rng = wrs.Range("alphabet") 'name of the first range

Dim RANGE_NAME As String
RANGE_NAME = Split(rng.Name, "!")(1)
RANGE_NAME = Replace(RANGE_NAME, "$", "") 'remove the dollar signs to be able to use the variable in the Docmd.transferspreadsheet function

Set rng = wrs.Range("numbers") 'name of the second range
Dim RANGE_NAME2 As String
RANGE_NAME2 = Split(rng.Name, "!")(1)
RANGE_NAME2 = Replace(RANGE_NAME2, "$", "")

Const FILE_PATH As String = "Q:\Fred\Book1.xlsx"
Const TABLE_NAME As String = "Caps"

DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:=TABLE_NAME, _
    FileName:=FILE_PATH, _
    HasFieldNames:=False, _
    Range:="Sheet 1!" & RANGE_NAME _
    
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:=TABLE_NAME, _
    FileName:=FILE_PATH, _
    HasFieldNames:=False, _
    Range:="Sheet 1!" & RANGE_NAME2 _
End Sub

I am reading in the columns based on range names I will have in advance.

My problem is that using the above code, the 2 columns are imported from excel successfully, but they are both imported into the same column. I thought if I used Docmd.transferspreadsheet twice then both pieces of data would be imported into the same table but in different columns.

Any advice on what I can do?

Thanks

[June7]

Why are you having to create new fields for import? Is this a routine process - how often? Sounds suspiciously like non-normalized data structure.

[aemara]

Thanks for your quick reply.

I could import the data from excel; that would be simple if I was just looking to import the whole table, but I'm only interested in 2 columns, and both have range names. So I want to make a table in Access out of these columns, thats why I need new fields.

And no its not a routine process, it'll be done once using this spreadsheet.

[June7]

If it's a one-time event then why not use the wizard? Can specify to exclude columns from import.

Or why not give a range name to both columns and import together?

How much data - maybe manually create the fields and just copy/paste?

[aemara]

This import is just one step in a bigger process. My aim is to code it all so that a user does not have to manually go in and do anything themselves, rather just have it all done using a click of a button; So i don't think a wizard is the best way to go.

And the data can go up to 150,000 records so copy/pasting is not an option.

But can you clarify on your second point? What do you mean import the columns together?

[June7]

I still don't understand why this is needed. This is a procedure to allow a user to do a one-time import of records from Excel? Many users have need to do one-time import? From the same Excel file from the same location?

Your code is referencing a range name for each column. Range Names are established in the spreadsheet, right? Why not set up range name that includes both columns? I've never had any success with the Range reference in TransportSpreadsheet but if it is working for you, congratulations.

An import specification can be saved and used with Docmd.RunSavedImportExport.

[aemara]

Well so far using a range name works when the range is one column, ie in the form A1:A10. When I try to name both columns at the same time, the range name returns a range in the form A1:A10,C1:C10, which apparently the Docmd.transferspreadsheet function cannot deal with.

In answer to your questions, this is a procedure to allow a user to do a one time import of records from Excel from the same Excel file from the same location.

As for the Docmd.RunSavedImportExport, I will look it up and see whether it'll get the task done then Ill get back to you.

Thanks again

[June7]

Maybe because the columns are not contiguous.

All these users need the same data imported? Why do they have to do it? Why don't you just do the import and give them the db?

Is this a multi-user db? Is it split design?

[aemara]

I think I see where you are going with this. It will be just one user, and he'll be using the whole process ( a process which develops and consolidates a database, and a process which also includes this import step) once every so many years. So the data over the years might eventually change, and the database will have to be redeveloped, so I can't just import and give them the db

[June7]

If the structure of the spreadsheet changes, then the import procedure would have to be modified. Certainly a saved specification would no longer apply.

And I am more confused by "a process which develops and consolidates a database".

[aemara]

I am building One big table in MS Access using sveral Excel tables, that's what I meant to say ( sorry for the vagueness). I read over saving an import specification and yea it won't do in this case. There's another way to get the job done. I could read in the 2 columns into 2 separate tables (using the Docmd.transferspreadsheet) and then use merge the tables. I just think this way is not very efficient and I was wondering if there was another simpler way using Docmd.transferspreadsheet, which apparently there might not be.

[June7]

Only other way I know is to open Excel objects in VBA and manipulate the objects. Pull in data one cell at a time. Much more complicated code.

[aemara]

Alright I'll stick to my method then.

I really appreciate you trying to help me out. Thanks again.