Newbie: Exporting Database to Excel from VB

[Magic]

I have a database called dbPrintWork.mdb.
This database contains a table called tblPrintWork.

I'm trying to export this table to Excel. So far I
can only get it into Excel by copying the table to the
clipboard and then patse to Excel.

I tried to open the dbPrintWork.mdb file in Excel
but it told me the "file was not in a recognizable
format"??

My idea was to try to use Excel to create a report
like Crystal.

Can this be done?
If so how can I export this Database with the selected data to Excel?


Any help will be appreciated!


Magic

[Paul Clement]

On 15 Apr 2002 06:40:49 -0800, "Magic" <magic1521@hotmail.com> wrote:

¤
¤ I have a database called dbPrintWork.mdb.
¤ This database contains a table called tblPrintWork.
¤
¤ I'm trying to export this table to Excel. So far I
¤ can only get it into Excel by copying the table to the
¤ clipboard and then patse to Excel.
¤
¤ I tried to open the dbPrintWork.mdb file in Excel
¤ but it told me the "file was not in a recognizable
¤ format"??
¤
¤ My idea was to try to use Excel to create a report
¤ like Crystal.
¤
¤ Can this be done?
¤ If so how can I export this Database with the selected data to Excel?
¤
¤

You can do this with DAO or ADO and a SQL statement. Here's a DAO example that you can work from:

Sub ExportToExcelDAO()

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim sqlString As String
Dim strSheetName As String

Set ws = Workspaces(0)
Set db = ws.OpenDatabase("d:\My Documents\db1.mdb")

strSheetName = "NewSheet"
sqlString = "SELECT * INTO [Excel 8.0;DATABASE=d:\My Documents\Book1.xls;HDR=No]." & strSheetName &
" FROM [Table1]"
db.Execute sqlString

End Sub


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[kherrerab]

I hope someone help me here!!

I had this code working just fine making it export from Access Table to Excel using ADO.

****************************************************


Public Sub Exportar()
On Error GoTo ManejaError
Dim Conn As New ADODB.Connection


CDB.DialogTitle = "Salvar Datos de Empleados"
CDB.Filter = "Archivos Excel (*.xls)|*.xls"
CDB.ShowSave
CDB.CancelError = True

If CDB.FileName = "" Then Exit Sub


Conn.Open SisSession.SisConeccion
' Conn.Execute "SELECT * INTO [BITACORA] IN '" & CDB.FileName & "' 'Excel 8.0;' FROM [Bitacora]"

MsgBox "Datos Exportados en " & Chr(13) & CDB.FileName, vbOKOnly + vbInformation

Exit Sub
ManejaError:
If Err.Number = 0 Then Exit Sub
ProcessError Err, Me.Name & " - Exportar", , , True
End Sub

****************************************************

Then i migrated the database to SQL Server 2000 and now it says that it does not recognize the syntaxis IN.

[VictorB]

I hope someone help me here!!

I had this code working just fine making it export from Access Table to Excel using ADO.

****************************************************


Public Sub Exportar()
On Error GoTo ManejaError
Dim Conn As New ADODB.Connection


CDB.DialogTitle = "Salvar Datos de Empleados"
CDB.Filter = "Archivos Excel (*.xls)|*.xls"
CDB.ShowSave
CDB.CancelError = True

If CDB.FileName = "" Then Exit Sub


Conn.Open SisSession.SisConeccion
' Conn.Execute "SELECT * INTO [BITACORA] IN '" & CDB.FileName & "' 'Excel 8.0;' FROM [Bitacora]"

MsgBox "Datos Exportados en " & Chr(13) & CDB.FileName, vbOKOnly + vbInformation

Exit Sub
ManejaError:
If Err.Number = 0 Then Exit Sub
ProcessError Err, Me.Name & " - Exportar", , , True
End Sub

****************************************************

Then i migrated the database to SQL Server 2000 and now it says that it does not recognize the syntaxis IN.

You are right, that doesn't appear to be correct SELECT syntax, according to SQL Server documentation.

I also work with VB and SQL Server. When I need to export data into an Excel file, I use a recordset and write into the file line by line.

For example, in your procedure you need to add the lines:

Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim fNr As Integer
Dim tmp As String

Then, after you open the connection:

rst.Open "SELECT * FROM [Bitacora]", Conn

While Not rst.EOF
  tmp = ""
  For Each fld in rst.Fields
    tmp = tmp & fld.Value & vbTab
  Next
  fNr = FreeFile
  Open CDB.FileName For Append As #fNr
  Print #fNr, tmp
  Close #fNr
Wend
rst.Close

This code produces a Tab-separated values file that Excel recognises.

You could also go the extra mile and use Excel and Worksheet objects instead of directly writing into the file, but I find these rather hard to use and not very well documented in MSDN.

[yhilewitz]

You might also try doing it from inside Excel itself, via VBA and ADO. First, make sure to have the ADO object library referenced.
Code as follows:

Dim adr As ADODB.Recordset
Dim adc As ADODB.Connection

Dim ConnStr as String 'Connection string
Dim SqlSTr as String 'Select statement

Set adr = New ADODB.Recordset
Set adc = New ADODB.Connection
adc.Open ConnStr
adr.Open SQLstr, adc, , , adCmdText
With ActiveWorkbook.ActiveSheet.QueryTables.Add(Connection:=adr, _
Destination:=Range("A1")) ' put the table starting at cell A1 - you can put it anywhere
.Refresh BackgroundQuery:=False
End With

[pclement]

See if the following works:

http://tinyurl.com/89mu4

[kherrerab]

This is what i did. It works great.

****************************************************

Public Sub Exportar()
On Error GoTo ManejaError
Dim strSheetName As String
Dim Conn As New ADODB.Connection


strSheetName = "Bitacora"
CDB.DialogTitle = "Salvar Datos de Empleados"
CDB.Filter = "Archivos Excel (*.xls)|*.xls"
CDB.FileName = "Bitacora"
CDB.ShowSave
CDB.CancelError = True
If CDB.FileName = "" Then Exit Sub
If Conn.State = 1 Then Conn.Close
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & CDB.FileName & "';Extended Properties=Excel 8.0;Persist Security Info=False"
strSheetName = "CREATE TABLE `Bitacora` (`IdUser` VarChar (3) ,`IdMenu` VarChar (20) ,`Fecha` DateTime ,`Referencia` VarChar (60) ,`Actividad` VarChar (120) ,`IP` VarChar (15) ,`HN` VarChar (15) )"
Conn.Execute strSheetName

If Conn.State = 1 Then Conn.Close
Conn.Open SisSession.SisConeccion
strSheetName = "insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=" & CDB.FileName & ";', 'SELECT * FROM [Bitacora]') " & AdoData.Source & ""
Conn.Execute strSheetName
MsgBox "Datos Exportados en " & Chr(13) & CDB.FileName, vbOKOnly + vbInformation
Exit Sub
ManejaError:
If Err.Number = 0 Then Exit Sub
ProcessError Err, Me.Name & " - Exportar", , , True
End Sub

[kherrerab]

This code works only when you have a local server, otherwise it gives you an error, can someone help me again!!

[pclement]

Your code or my code? Did you check out the link I posted?

[kherrerab]

my code.

It rises an error when used with remote server on LAN. When used with local works grat.

[kherrerab]

Hello!!!!!!!

[kherrerab]

Hey

[pclement]

I don't think you answered my question. Which code are you referring to? Also, what is the error?

[kherrerab]

This is what i did. It works great only with local server. I need to make it work with a remote server.

*****************************************************

Public Sub Exportar()
On Error GoTo ManejaError
Dim strSheetName As String
Dim Conn As New ADODB.Connection


strSheetName = "Bitacora"
CDB.DialogTitle = "Salvar Datos de Empleados"
CDB.Filter = "Archivos Excel (*.xls)|*.xls"
CDB.FileName = "Bitacora"
CDB.ShowSave
CDB.CancelError = True
If CDB.FileName = "" Then Exit Sub
If Conn.State = 1 Then Conn.Close
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & CDB.FileName & "';Extended Properties=Excel 8.0;Persist Security Info=False"
strSheetName = "CREATE TABLE `Bitacora` (`IdUser` VarChar (3) ,`IdMenu` VarChar (20) ,`Fecha` DateTime ,`Referencia` VarChar (60) ,`Actividad` VarChar (120) ,`IP` VarChar (15) ,`HN` VarChar (15) )"
Conn.Execute strSheetName

If Conn.State = 1 Then Conn.Close
Conn.Open SisSession.SisConeccion
strSheetName = "insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=" & CDB.FileName & ";', 'SELECT * FROM [Bitacora]') " & AdoData.Source & ""
Conn.Execute strSheetName
MsgBox "Datos Exportados en " & Chr(13) & CDB.FileName, vbOKOnly + vbInformation
Exit Sub
ManejaError:
If Err.Number = 0 Then Exit Sub
ProcessError Err, Me.Name & " - Exportar", , , True
End Sub

[pclement]

You still haven't identified why it isn't working or any errors you're encountering.