Export .mdb Table to .xls format 1

[LiLgUrL]

In Access we can export an .mdb table to .xls format with this code:

DoCmd.OutputTo acOutputTable, "Table1", "MicrosoftExcel(*.xls)", "\MyTable\" & "WorkSheet1.xls"

I want to do the same thing using VB6 but don't know how? Please guide me on how to do the stuff thanx!

 

[Hypetia]

You can use the DoCmd object of Access application object to do this job.
___
[tt]
Dim AccessApp As Object
Set AccessApp = CreateObject("Access.Application&quot
AccessApp.OpenCurrentDatabase "D:\TAC.MDB" 'path of the mdb file
AccessApp.DoCmd.OutputTo 0, "Table1", "MicrosoftExcel(*.xls)", "\MyTable\WorkSheet1.xls"
AccessApp.Quit
Set AccessApp = Nothing[/tt]
___

Here, I have used late-binding but you can also use early-binding to improve performance.

 

[Swi]

Or . . .

' Add a reference to Microsoft Active X Data Objects X.X Library
Dim CONN As New ADODB.Connection
Dim NUMOFRECORDS As Long
Screen.MousePointer = vbHourglass
DoEvents
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "MDB_PATH_AND_NAME" & _
";"
CONN.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=XL_PATH_AND_NAME].[Books] FROM " & _
"TABLE NAME", NUMOFRECORDS
CONN.Close
Set CONN = Nothing
Screen.MousePointer = vbDefault
MsgBox NUMOFRECORDS & " records copied!", vbInformation


Swi

 

[LiLgUrL]

Thanx for the 2 of you... You just helped me solve my problem...
And before i forget... how about on the other way around?
i want to import the xls into my existing table with same format? I mean i want to fetch the contents of the xls?

 

[Swi]

thread222-683416

Swi

 

[LiLgUrL]

hi swi!

' Add a reference to Microsoft Active X Data Objects X.X Library
Dim CONN As New ADODB.Connection
Dim NUMOFRECORDS As Long
Screen.MousePointer = vbHourglass
DoEvents
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "MDB_PATH_AND_NAME" & _
";"
CONN.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=XL_PATH_AND_NAME].[Books] FROM " & _
"TABLE NAME", NUMOFRECORDS
CONN.Close
Set CONN = Nothing
Screen.MousePointer = vbDefault
MsgBox NUMOFRECORDS & " records copied!", vbInformat

your code is working well ... but there's one thing i can't get... what if i want to select multiple table(1 table-1 worksheet) so if i have 3 tables i'll get 3 worksheets in one excel file. how's that? and the file name i want it something like XL_PATH_AND_NAME + date_today

regards,
~gwen~

 

[mmilan]

Give SWI the star for this, because I've just parameterised the code he had already written...

' Add a reference to Microsoft Active X Data Objects X.X Library
public sub AccessToXL(sAccessFile as string, sExcelFile as string, sTable as string)

Dim CONN As New ADODB.Connection
Dim NUMOFRECORDS As Long
Screen.MousePointer = vbHourglass
DoEvents
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sAccessFile & _
";"
CONN.Execute "SELECT * INTO [Excel 8.0;" & _
"Database="+sexcelfile+"].[Books] FROM " & _
sTable, NUMOFRECORDS
CONN.Close
Set CONN = Nothing
Screen.MousePointer = vbDefault
MsgBox NUMOFRECORDS & " records copied!", vbInformat
end sub

 

[mmilan]

vbInformat should be vbInformation

 

[LiLgUrL]

hi mmilan,

thanks for the reply but there's a but

your code is working well ... but there's one thing i can't get... what if i want to select multiple table(1 table-1 worksheet) so if i have 3 tables i'll get 3 worksheets in one excel file.

 

[LiLgUrL]

oh... never mind i figured it out... thanks to all of you... til then!