Export whole table with headers from Excel to Access

Hi,

I am using similar code to transfer data from Excel to Access:

http://stackoverflow.com/questions/1...s-access-table

This first code is working fine but when you have >50 names of columns to write in string in SQL command - it can be hard.


In the same source is another code connected with TransferSpreadsheet method -maybe it will be alternative method to copy whole table with headers to Access (create table automatically)?

I am trying to use it to transport my table from sheet "Główny" to Access:

Sub test()

Dim Question As String
Dim Connectstr As String
Dim HurtowniaADO As Object
Dim ZdanieSQL As String
Dim Login As String
Dim FileName As String
Dim Lokalizacja_Pliku As String
Dim Lokalizacja_Folderu As String
Dim acc As Access.Application

Set HurtowniaADO = CreateObject("ADODB.Connection")

Question = MsgBox("Czy przegrać dane do Bazy?", vbYesNo)
If Question = vbNo Then
End
End If

Lokalizacja_Pliku = "C:\Users\ljar01\Desktop\Makro\Makro Brak Raportów\Brak_Raportow_Baza.accdb"
Lokalizacja_Folderu = "C:\Users\ljar01\Desktop\Makro\Makro Brak Raportów\"

FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"

Login = Environ("Username")

Set acc = New Access.Application
acc.OpenCurrentDatabase Lokalizacja_Pliku
acc.DoCmd.TransferSpreadsheet , _
transfertype:=acImport, _
TableName:="tblExcelImport", _
FileName:=Application.ActiveWorkbook.FullName, _
HasFieldNames:=True, _
Range:="Tabela29"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing

End Sub
The example does not work. acImport - there is no variable here (why should i assing variable here if it is a property of TransferSpreadsheet method?)
Maybe you have any ideas ?

Jacek

p.s. in attachment my workbook and in sheet This workbook on workbook_close you have my code there.

Hi,

I am using similar code to transfer data from Excel to Access:

http://stackoverflow.com/questions/1...s-access-table

This first code is working fine but when you have >50 names of columns to write in string in SQL command - it can be hard.


In the same source is another code connected with TransferSpreadsheet method -maybe it will be alternative method to copy whole table with headers to Access (create table automatically)?

I am trying to use it to transport my table from sheet "Główny" to Access:

Sub test()

Dim Question As String
Dim Connectstr As String
Dim HurtowniaADO As Object
Dim ZdanieSQL As String
Dim Login As String
Dim FileName As String
Dim Lokalizacja_Pliku As String
Dim Lokalizacja_Folderu As String
Dim acc As Access.Application

Set HurtowniaADO = CreateObject("ADODB.Connection")

Question = MsgBox("Czy przegrać dane do Bazy?", vbYesNo)
If Question = vbNo Then
End
End If

Lokalizacja_Pliku = "C:\Users\ljar01\Desktop\Makro\Makro Brak Raportów\Brak_Raportow_Baza.accdb"
Lokalizacja_Folderu = "C:\Users\ljar01\Desktop\Makro\Makro Brak Raportów\"

FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"

Login = Environ("Username")

Set acc = New Access.Application
acc.OpenCurrentDatabase Lokalizacja_Pliku
acc.DoCmd.TransferSpreadsheet , _
transfertype:=acImport, _
TableName:="tblExcelImport", _
FileName:=Application.ActiveWorkbook.FullName, _
HasFieldNames:=True, _
Range:="Tabela29"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing

End Sub
The example does not work. acImport - there is no variable here (why should i assing variable here if it is a property of TransferSpreadsheet method?)
Maybe you have any ideas ?

Jacek

p.s. in attachment my workbook and in sheet This workbook on workbook_close you have my code there.

Hi,

It is a matter of preference. If you do not use Call you must remove the brackets around the argument list.