Convert Excell to Mdb 1

[Vec]

I will try once more!
Using Visual Basic! Not the tools in Excel or Access, Using Visual Basic (a command button)
Can someone PLEASE show me how to convert an excel db to access.
The excel db will have only one sheet and it just needs to be converted to one access table.

Also the excel db will always be exactly the same db (filed wise) so it can be hard coded into the code, I actually prefer this so that the user can't screw up! It should take
C:\mydata.xls and convert it to C:\mydata.mdb with the table in access named log
I need to build this capability into a vb project.

Anyone???? -

Looking Toward The Future

http://www.webfusiondesign.com

 

[kevinclark]

'Set following VB project References:
'Microsoft Excel 9 Object Library
'Microsoft Access 9 Object Library

Dim xl As Excel.Application
Dim ac As Access.Application

Private Sub Command1_Click()

Dim SourceFile As String
Dim TargetFile As String
Set xlSource = New Excel.Application
Set acTarget = New Access.Application

SourceFile = "C:\Development\Database\TEST\Source.xls"
TargetFile = "C:\Development\Database\TEST\TEST.mdb"

acTarget.OpenCurrentDatabase TargetFile, False
xlSource.Workbooks.Open SourceFile

acTarget.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "LOG", SourceFile, True, , False

End Sub

 

[Vec]

Thanks Kevin, works exactly as I needed! -

Looking Toward The Future

http://www.webfusiondesign.com

 

[kevinclark]

Hi Vec

Glad it helped. One thing I should mention is that at each execution of the code, a new instance of Excel and Access will be created. Therefore you will need to be sure to add a couple of lines after the import to kill each of these instances.


xlSource.Close
acTarget.close