CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Problem getting data from Access to Excel

  1. #1
    Join Date
    Jun 2002

    Problem getting data from Access to Excel

    Help please:

    I want to transfer information from Access 2000 to Excel 2000. I used the excel macro wizard to get the code and it's working. But if I use this code in different computers with different procesor speed, sometimes I can not see the result displayed in excel. That's the reason why I put the FOR. If the computer is faster, I need to increase the loop to see the result.

    -Does anybody have an idea why?
    -Is there any other solution to get data from Access to Excel?


    Dim db As Database
    Dim connstring As String
    Dim Rs As Recordset
    Dim sql As String
    Dim Hope As integer

    Set db = OpenDatabase("c:\mydatabase.mdb")

    'From here, I took it from Excel Macro wizard

    connstring = "ODBC;DSN=MS Access Database;DBQ=c:\mydatabase.mdb;DefaultDir=c:\;DriverId=25;FIL=MS"

    sql = "SELECT field1, field2 " _
    & Chr(13) & "" & Chr(10) & "FROM Table "


    'I don't want to use this FOR, but If I remove it, it doesn't work

    For Z = 1 To 1000000
    HOPE = 0
    Next Z

    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
    Destination:=sheet1.Range("a1"), sql:=sql)

    .FieldNames = True
    .RefreshStyle = xlInsertDeleteCells
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .RefreshOnFileOpen = False
    .HasAutoFormat = True
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SavePassword = True
    .SaveData = True
    End With

    End Sub

  2. #2
    Join Date
    Aug 2002
    I use DoCmd.TransferSpreadsheet to Export a Table. Then I optimize the Spreadsheet with

    Set xlsObj = CreateObject("Excel.Application")
    Set xlsObj = GetObject(strFilename, "Excel.Sheet")

    so I can directly manipulate my exported Spreadsheet. Like VBA in the Excel App. .
    Best regards

Posting Permissions

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

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)