-
August 19th, 2002, 04:19 AM
#1
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
-
August 19th, 2002, 06:40 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
On-Demand Webinars (sponsored)
|