I urgently need to get a data from a certain table in Access and paste them into the cells in an exisitng Excel file using a VB macro. Any hel is greatly appreciated! Thanks in advance!
---
---
---
---
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Data from Access to Excel
- Thread starter Rydel
- Start date
jgoodman00
Programmer
- Jan 23, 2001
- 1,510
Have you looked at the Docmd.TransferSpreadSheet method?
However, this method does not work with SQL server views or stored procedures yet... James Goodman
However, this method does not work with SQL server views or stored procedures yet... James Goodman
Excel has a tool for this...
In excel select Data>Get External Data>New Data base Query,... then from the databses tab, choose "Ms Access Database*"...
follow the wizard from there...
Once you have returned your data to excel,..right click in the data area and choose properties...there you can set options like refresh on open and auto fill formulas etc....
In excel select Data>Get External Data>New Data base Query,... then from the databses tab, choose "Ms Access Database*"...
follow the wizard from there...
Once you have returned your data to excel,..right click in the data area and choose properties...there you can set options like refresh on open and auto fill formulas etc....
- Thread starter
- #4
ETID: I was interested in the programming solution, because it's all a part of the database that will be shipped to managers who want just one button to click 
. But thanks anyway.
jgoodman00: that sounds interesting, but I just did this (omitting the beginning - with conneciton string and opening a connection and recordset):
myRst.Open "[tblMyTable]", con, adOpenDynamic, adLockPessimistic
Do Until myRST.EOF
Worksheets(strCurrentSheet).Cells(i, 1).Value = myRST![ID]
Worksheets(strCurrentSheet).Cells(i, 2).Value = myRST![Name]
i = i + 1
myRST.MoveNext
Loop
But thank you!
Now I have another question
My next problem is that certain fields are not exported correctly. Instead I have #VALUE! in the field. Any suggestions? ---
---
. But thanks anyway.jgoodman00: that sounds interesting, but I just did this (omitting the beginning - with conneciton string and opening a connection and recordset):
myRst.Open "[tblMyTable]", con, adOpenDynamic, adLockPessimistic
Do Until myRST.EOF
Worksheets(strCurrentSheet).Cells(i, 1).Value = myRST![ID]
Worksheets(strCurrentSheet).Cells(i, 2).Value = myRST![Name]
i = i + 1
myRST.MoveNext
Loop
But thank you!
Now I have another question
My next problem is that certain fields are not exported correctly. Instead I have #VALUE! in the field. Any suggestions? ---
---
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question