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
gszPersonenQuer y = "SELECT table1.* FROM ... INNER "

Dim qTemp As QueryDef
Dim szSQL As String

Set qTemp = CurrentDb.Query Defs("aTemp")
qTemp.sql = gszPersonenQuer y
DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.xl s"

[Karl Irvin]

File>Export, change type to Excel
<ke******@web.d e> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.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
gszPersonenQuer y = "SELECT table1.* FROM ... INNER "

Dim qTemp As QueryDef
Dim szSQL As String

Set qTemp = CurrentDb.Query Defs("aTemp")
qTemp.sql = gszPersonenQuer y
DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.xl s"

[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").Sel ect

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

ActiveCell.Offs et(0, 1) = rst.Fields("Fie ld1").Value
ActiveCell.Offs et(0, 2) = rst.Fields("Fie ld2").Value
ActiveCell.Offs et(0, 3) = rst.Fields("Fie ld3").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
gszPersonenQuer y = "SELECT table1.* FROM ... INNER "

Dim qTemp As QueryDef
Dim szSQL As String

Set qTemp = CurrentDb.Query Defs("aTemp")
qTemp.sql = gszPersonenQuer y
DoCmd.OutputTo acOutputQuery, "aTemp", acFormatXLS,
"C:\Personen.x ls"

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

[jahoobob via AccessMonster.com]

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

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

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").Se lect

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

ActiveCell.Offs et(0, 1) = rst.Fields("Fie ld1").Value
ActiveCell.Offs et(0, 2) = rst.Fields("Fie ld2").Value
ActiveCell.Offs et(0, 3) = rst.Fields("Fie ld3").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