docmd.transferspreadsheet no column headings

[laughingdevil]

Hi

Hopefully someone can point me in the right direction on this.

I'm trying to speed up the import section of a DB I've inherited by doing the import using the docmd.transferspreadsheet method, however I'm having problems.

In short I seem to be able to either select the column headings in the excel file, or use F1,F2 etc.

What I want to do is simply put column A in excel in the first column in access all the way to column GA.

Does anyone know a way to do this?

For information I am using Excel 2007, Access 2002, and I can't use the column headings from the xls. The reason for this (as someone is bound to ask) is because the xls is an export from one of about 20 other systems, the fieldnames are therefore labled differently, though the extraction software ensures they are in the same order, and I can't change the extraction software.

Any help would be appreciated.

 

[Automattic]

Can you elaborate on the column headings issue with the 20 different fieldname variations?

I had an issue where the column headings had essentially the same name but depending on the source would be in a different order. To solve that I had a form that would allow me to point to the xlsx file i wanted, and then run a vba script that: inserted a column heading row, renamed the columns according to something useful, rearrange the columns, and then run the docmd.transfer bit.

 

[laughingdevil]

That sounds similar Automattic, the columns are in the right order, so the headings are essentially the same, but not quite (some have underscores between words, some have spaces, some words joined, some the same thing using a similar but different word)

Manually inserting a row of column headings where I want them sounds like a good idea, thanks for that tip.

how did you do the column rearrange? Not somthing I need ofr this, but somthing I may need later for another bit of the project.

 

[Automattic]

If it's something that can be automated (for me it was), you can rename your columns in vb. So what I did was run a code like this:

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook

Dim i As Integer
Dim k As Integer
Dim l As Integer


Set Xl = CreateObject("Excel.Application")
Set XlBook = Xl.Workbooks.Open(MySheetPath)

'Make sure excel is visible on the screen 
'this can be useful later for editing

Xl.Visible = True
XlBook.Windows(1).Visible = True



'if you have static column headings (which sounds like you do), then it's not 'too hard to rename them, just use the following and add another range and 'column heading for each one you need:



Xl.Sheets(1).range("A1") = "dtmdate"
Xl.Sheets(1).range("B1") = "strsiteid"

if you have the similar column names but they'll be in a different order, let me know and i'll post that one too. You basically use a series of if statements in a loop.

I posted a generic approach on reordering columns here:
http://www.mrexcel.com/forum/showthread.php?t=565133&highlight=shifting+columns&page=2

but there is one little error. You need to add the following code to the end of the second loop:

        Xl.ActiveSheet.range("A1").Activate
        Xl.ActiveCell.Offset(0, L).Select

This just makes it so that after you copy a column it goes back to the source column.

Hope this helps,

-A

 

[laughingdevil]

Thanks for that, I've got 190 columns so I'll be doing a few lines of code there But better to do it right once!

If you could post the rearrrange code that would be great, I'd like to know how to do it even if I don't need to as I think I'll need it for my next project.