access -> excel

[kenduron]

Hello together,

I simply want to export table data to a new excel file without saving
this file to local hard disk drive, the user should decide where to
store.

Alltogether, the excel sheet should only exist in memory

Thanks, Karsten

Previous solution: Exporting custom query to Temp-File

'Custom-Query
gszPersonenQuery = "SELECT table1.* FROM ... INNER "

Dim qTemp As QueryDef
Dim szSQL As String

Set qTemp = CurrentDb.QueryDefs("aTemp")
qTemp.sql = gszPersonenQuery
DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.xls"

[Karl Irvin]

File>Export, change type to Excel
<ke******@web.de> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

Hello together,

I simply want to export table data to a new excel file without saving
this file to local hard disk drive, the user should decide where to
store.

Alltogether, the excel sheet should only exist in memory

Thanks, Karsten

Previous solution: Exporting custom query to Temp-File

'Custom-Query
gszPersonenQuery = "SELECT table1.* FROM ... INNER "

Dim qTemp As QueryDef
Dim szSQL As String

Set qTemp = CurrentDb.QueryDefs("aTemp")
qTemp.sql = gszPersonenQuery
DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.xls"

[jahoobob via AccessMonster.com]

Use the ADO method in a Excel macro. Data is imported into the current
spreadsheet and if the user wants it saved he may do so or not save and it is
gone.
Not knowing you data structure, here are the basics for the Excel macro:

Sub OpenaRecoedset()
Dim rst As ADODB.Recordset

Set rst = New Recordset

Range("A1").Select

rst.Open "SELECT * FROM table name", _
"DRIVER=Microsoft Access Driver (*.mdb);" & _
"DBQ=path of the access database;", _
adOpenKeyset, adLockPessimistic

ActiveCell.Offset(0, 1) = rst.Fields("Field1").Value
ActiveCell.Offset(0, 2) = rst.Fields("Field2").Value
ActiveCell.Offset(0, 3) = rst.Fields("Field3").Value

rst.MoveNext

rst.Close
End Sub

Of course you will need to substitute the table's name and the path ot the db
where it says in the code I have posted.

ke******@web.de wrote:

Hello together,

I simply want to export table data to a new excel file without saving
this file to local hard disk drive, the user should decide where to
store.

Alltogether, the excel sheet should only exist in memory

Thanks, Karsten

Previous solution: Exporting custom query to Temp-File

'Custom-Query
gszPersonenQuery = "SELECT table1.* FROM ... INNER "

Dim qTemp As QueryDef
Dim szSQL As String

Set qTemp = CurrentDb.QueryDefs("aTemp")
qTemp.sql = gszPersonenQuery
DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.xls"

--
Message posted via http://www.accessmonster.com

[jahoobob via AccessMonster.com]

Oops.
Add:
i = 0 after Range("A1").Select

Do Until rst.EOF
i = i + 1 after adOpenKeyset, adLockPessimistic

Loop after rst.MoveNext

jahoobob wrote:

Use the ADO method in a Excel macro. Data is imported into the current
spreadsheet and if the user wants it saved he may do so or not save and it is
gone.
Not knowing you data structure, here are the basics for the Excel macro:

Sub OpenaRecoedset()
Dim rst As ADODB.Recordset

Set rst = New Recordset

Range("A1").Select

rst.Open "SELECT * FROM table name", _
"DRIVER=Microsoft Access Driver (*.mdb);" & _
"DBQ=path of the access database;", _
adOpenKeyset, adLockPessimistic

ActiveCell.Offset(0, 1) = rst.Fields("Field1").Value
ActiveCell.Offset(0, 2) = rst.Fields("Field2").Value
ActiveCell.Offset(0, 3) = rst.Fields("Field3").Value

rst.MoveNext

rst.Close
End Sub

Of course you will need to substitute the table's name and the path ot the db
where it says in the code I have posted.

Hello together,

[quoted text clipped - 18 lines]

DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.xls"

--
Message posted via http://www.accessmonster.com