Newbie: Exporting Database to Excel from VB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: Newbie: Exporting Database to Excel from VB

  1. #1
    Magic Guest

    Newbie: Exporting Database to Excel from VB


    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

  2. #2
    Paul Clement Guest

    Re: Newbie: Exporting Database to Excel from VB

    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)

  3. #3
    Join Date
    Oct 2005
    Posts
    18
    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.

  4. #4
    Join Date
    Nov 2005
    Posts
    27
    Quote Originally Posted by 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.

    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:

    Code:
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim fNr As Integer
    Dim tmp As String
    Then, after you open the connection:
    Code:
    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.

  5. #5
    Join Date
    Feb 2004
    Posts
    27
    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

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    See if the following works:

    http://tinyurl.com/89mu4
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  7. #7
    Join Date
    Oct 2005
    Posts
    18
    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

  8. #8
    Join Date
    Oct 2005
    Posts
    18
    This code works only when you have a local server, otherwise it gives you an error, can someone help me again!!

  9. #9
    Join Date
    Dec 2003
    Posts
    2,750
    Your code or my code? Did you check out the link I posted?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  10. #10
    Join Date
    Oct 2005
    Posts
    18
    my code.

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

  11. #11
    Join Date
    Oct 2005
    Posts
    18
    Hello!!!!!!!


  12. #12
    Join Date
    Oct 2005
    Posts
    18
    Hey

  13. #13
    Join Date
    Dec 2003
    Posts
    2,750
    I don't think you answered my question. Which code are you referring to? Also, what is the error?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  14. #14
    Join Date
    Oct 2005
    Posts
    18
    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

  15. #15
    Join Date
    Dec 2003
    Posts
    2,750
    You still haven't identified why it isn't working or any errors you're encountering.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center