dcsimg
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
    Location
    China
    Posts
    5

    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?

    Thanks


    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 "

    sheet1.Activate
    sheet1.Cells.ClearContents

    '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
    Location
    Germany
    Posts
    15
    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
    Markus

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)