Hi,
I am fairly new to using VBA in Excel to carry out actions in Access (ADO). I have found a piece of code online to create a DB and then create a standard table.
I want to create a table that is linked to a excel spreadsheet. The code to do this (taken from Access module) is:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "CLV_New_test", "C:Documents and SettingsMylesDesktopclvCLV Data Template.xls", True, ""
However when I add this into my code the DB is created successfully but I get an error message on the above line. Runtime error 424 object required. I'm sure I'm missing something simple but after 4 hours of trawling online and through some books I've not come up with anything.
I have Microsoft ActiveX Data Objects Library set up.
Here is my full code:
Sub CreateDatabase()
'Original Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Create an Access database on the fly
'http://www.excelguru.ca/node/60
Dim Cnct As String, Src As String
Dim Catalog As Object
Dim Connecion As ADODB.Connection
Dim dbPath As String
Dim DB_Name As String
Dim DBFullName As String
DB_Name = Range("DBase_Name").Text
' Database information
If Range("config_DB_InDir").Value = True Then
DBFullName = ThisWorkbook.Path & "" & DB_Name & "Test.mdb"
Else
DBFullName = Range("Database_Path").Text
End If
'Set database name here
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";"
'Create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create Cnct
Set Catalog = Nothing
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "CLV_New_test", "C:Documents and SettingsMylesDesktopclvCLV Data Template.xls", True, ""
Connection.Close
Set Connection = Nothing
End Sub
Any ideas would be greatfully received.
Thanks,
Myles
I am fairly new to using VBA in Excel to carry out actions in Access (ADO). I have found a piece of code online to create a DB and then create a standard table.
I want to create a table that is linked to a excel spreadsheet. The code to do this (taken from Access module) is:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "CLV_New_test", "C:Documents and SettingsMylesDesktopclvCLV Data Template.xls", True, ""
However when I add this into my code the DB is created successfully but I get an error message on the above line. Runtime error 424 object required. I'm sure I'm missing something simple but after 4 hours of trawling online and through some books I've not come up with anything.
I have Microsoft ActiveX Data Objects Library set up.
Here is my full code:
Sub CreateDatabase()
'Original Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Create an Access database on the fly
'http://www.excelguru.ca/node/60
Dim Cnct As String, Src As String
Dim Catalog As Object
Dim Connecion As ADODB.Connection
Dim dbPath As String
Dim DB_Name As String
Dim DBFullName As String
DB_Name = Range("DBase_Name").Text
' Database information
If Range("config_DB_InDir").Value = True Then
DBFullName = ThisWorkbook.Path & "" & DB_Name & "Test.mdb"
Else
DBFullName = Range("Database_Path").Text
End If
'Set database name here
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";"
'Create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create Cnct
Set Catalog = Nothing
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "CLV_New_test", "C:Documents and SettingsMylesDesktopclvCLV Data Template.xls", True, ""
Connection.Close
Set Connection = Nothing
End Sub
Any ideas would be greatfully received.
Thanks,
Myles