Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export data via VB6 to Excel (xp pro)

    Trying to export data from an Access table via VB6 to Excel. It works when saving the file. The problem I am having is if the user does not save (clicks cancel), the program (Excel) sometimes hangs. Presently, Excel is not opened but when asked to save the file, the saveas gui opens. My work around is to error trap and exit sub. If possible, I would like that part to be silent also. Here is what I have so far:

    Public Sub ExportDataExcel(ByRef sFileName As String, ByRef iNum As Integer, bFileSaved As Boolean)
    On Error GoTo ErrHandle
    Dim cn As New ADODB.Connection
    Dim objExcelApp As Excel.Application
    Dim xlsExcelSheet As Excel.Worksheet
    Dim col As Integer
    Dim Row As Integer
    Dim RecNum As Integer

    Set objExcelApp = New Excel.Application
    objExcelApp.Workbooks.Add

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "temp.mdb" & _
    ";Persist Security Info=False"
    conn.Open
    If rs.State = adStateOpen Then rs.Close
    sSql = "SELECT * FROM Members"
    rs.Open sSql, conn, , , adCmdText
    For col = 0 To rs.Fields.Count - 1 ' Make the column headers.
    xlsExcelSheet.Cells(1, col + 1) = rs.Fields(col).Name
    Next col
    If Dir$(App.Path & "book1.xls") <> vbNullString Then Kill App.Path & "book1.xls"
    conn.Execute "SELECT * INTO [Excel 8.0; Database=" & App.Path & "book1.xls].[Sheet1]" & _
    " FROM Members", iNum

    Row = 2
    Do While Not rs.EOF
    For col = 0 To rs.Fields.Count - 1
    xlsExcelSheet.Cells(Row, col + 1) = rs.Fields(col).Value
    Next col
    Row = Row + 1
    rs.MoveNext
    .Visible = True
    End With
    Loop
    objExcelApp.DisplayAlerts = False
    objExcelApp.DefaultFilePath = App.Path
    objExcelApp.Application.SaveWorkspace (App.Path & "book1") 'If user cancels, this hangs. Also how to have this silent?
    objExcelApp.Quit
    rs.Close
    conn.Close
    Set conn = Nothing
    cn.Close
    Set cn = Nothing
    Kill App.Path & "temp.mdb" 'remove temp.mdb
    bFileSaved = True
    ExportDataExcel_Exit:
    Set xlsExcelSheet = Nothing
    Set objExcelApp = Nothing
    Exit Sub
    ErrHandle:
    'objExcelApp.Quit
    Set xlsExcelSheet = Nothing
    Set objExcelApp = Nothing
    bFileSaved = False
    Exit Sub
    End Sub


    And last, if I use a variable such as sFileName in place of "book1.xls", error says it can not access the file.

    Thanks for the help.

    Kim

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Export data via VB6 to Excel (xp pro)

    I'm not a big Excel user, so these comments are more from the perspective of automating Word.

    Rather than use the native "save" command, forcing Excel to display any necessary dialogs --

    objExcelApp.Application.SaveWorkspace

    -- why not use Excel's Dialogs collection to display the SaveAs dialog? This should return a value you can check so you know whether the user saved or canceled.

    I now return you to people who know something about Excel. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data via VB6 to Excel (xp pro)

    Why are you using the SaveWorkspace method as opposed to the normal SaveAs method?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data via VB6 to Excel (xp pro)

    objExcelApp.ActiveWorkbook.SaveAs ("book1.xls") works.

    Thanks for the help.

    Kim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •