Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: Access working with Windows 7

    I have an Access VBA procedure that creates an Excel spreadsheet from Access data. This works fine in XP but does not work with Windows 7. Has anyone any ideas about why? The code is:

    Dim stDocName As String

    DoCmd.SetWarnings False
    stDocName = "qryExportThree"
    'Create the table tblExport
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    'Create a Recordset from all the records in the tblExport table
    Dim MyData As String
    Dim conn As New adodb.Connection
    Dim rs As adodb.Recordset
    MyData = DLookup("Path", "tblAdmin")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyData & ";"
    conn.CursorLocation = adUseClient
    Set rs = conn.Execute("tblExport", , adCmdTable)

    'Create a new workbook in Excel
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Add
    Set oSheet = oBook.worksheets(1)

    'Transfer the data to Excel
    oSheet.range("A1").copyfromrecordset rs

    'Save the Workbook and Quit Excel
    oBook.SaveAs Me.txtdir
    oExcel.Quit

    'Close the connection
    rs.Close
    conn.Close

    DoCmd.SetWarnings True

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    to create an excel sheet - why the New adodb.Connection?

    not an answer sorry;
    www CahabaData com

Posting Permissions

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