Transferring Excel Columns to Access Fields

[mahmud1180]

Hi there!! I want to run a VBA code that makes a column in the excel file and then transfer it to Access with a certain Field Name. Then this excel Column is replaced by another set of Values and I want to transfer it to Access with another Field Name.I have a recurring procedure so, I can not use the AccessImport Method. I can add values cell by cell but I want to avoid that as it is gonna take more time. Can anyone help? Thanks a lot!!

 

[sheetspread]

If that doesn't work it may be possible to add the headers in the temp table before appending

 

[mahmud1180]

Wait I'll try to find a way to turn off the headers import

HDR = No?

(in the first code you tried)

HDR=NO... Gives an error indicating F1 as the unknown field name.

I kept the HDR=Yes but kept the first value in the excel range same as the field name. That did the trick actually :D

Thanks a lot SheetSpread

 

[mahmud1180]

When I am inserting the range in the Access Field it is inserting like :
1
2
3

1
2
3​

1
2
3​

Is there any way I could put them like
111
222
333

?

Thanks

 

[sheetspread]

Hmmmm, I'm trying to figure out why it's importing one field at a time and starting on a new record in between. Which code are you using?

 

[mahmud1180]

Does this help?

sql - Using Excel VBA to export data to MS Access table - Stack Overflow

I am using this code now. This was the first link you gave

 

[sheetspread]

Is it the first code in the link, with a loop to add from Excel to the recordset?

 

[mahmud1180]

Is it the first code in the link, with a loop to add from Excel to the recordset?

I am using the this code
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Public Sub DoTrans()


Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\FDData.mdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn

ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh


cn.Execute ssql


End Sub</code>

 

[sheetspread]

I tested the code and it did work even with Access 2003, but headers were required in the spreadsheet. One way around this may be to add lines to the code that insert a row with headers (either hard coded or obtained through a fieldname loop) then delete the row afterwards. I'll try to put that together.

 

[sheetspread]

Richard Schollar's post here:

http://www.mrexcel.com/forum/excel-...ons-import-access-data-table-field-names.html (#3)

gets the field names into Excel, (delete or comment out this line: .Range("A3").CopyFromRecordset rs which pastes the table when you want just the field names)

You can then add a line that inserts a row, puts those field names there, and reverses the process afterwards if you like.

 

[mahmud1180]

When I am inserting the range in the Access Field it is inserting like :
1
2
3

1
2
3​

1
2
3​

Is there any way I could put them like
111
222
333

?

Thanks

Thank you for your reply I will have unique header in the excel for every columns. So heading won't be a problem. But in access the columns are inserted after the last row of the previous Columns(or Fields). If the First field has 100 rows, the inserted next field will start from row 101. It continues like that. The number of rows will grow significantly large if I insert many Fields. Is it possible to insert the values from the 1st row till 100 rows for all the inserted fields. I tried deleting the blanks but could only delete the whole rows not cells. Thanks