Writing query data in chained excel table

[Michael Aggerholm]

Hello all you sheer genius'es ;o)

Running a group of queries in access, I have suddenly been given the task to
change some data in a chained excel worksheet.

I have made a query to generate a result similar to that in the worksheet,
but havent found a way to write them into the sheet without adding them to
the data already in there.

In general, the worksheet format is a 5x5 frame of 25 different values which
is used in the workbooks other worksheets.

I can make this by importing the query from excel, but it would be that much
easier if Access could force the data upon the worksheet.

Did I make any sense ?
(If so, it's most likely accidental) ;o)

Greetings

Michael

 

[Ken Snell [MVP]]

You should be able to do this with TransferSpreadsheet, but you'll need to
know the worksheet name onto which the data need to be written, the top
leftmost cell that contains the first data item, and the EXCEL filename and
path.

If you name your query that you want to export the same as the worksheet
name, then TransferSpreadsheet should write onto that same worksheet.
Otherwise, you can try using the Range argument of the TransferSpreadsheet
method when you export, where the Range is the name of the worksheet.

If the first cell with data is not A1, then you will probably need to use
Automation to write the data directly into EXCEL.

Here are some examples of various ways to export data from ACCESS to EXCEL
using TransferSpreadsheet or Automation:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

 

[Michael Aggerholm]

So I take the sniplet below and puts into a VB module, and runs the module ?

-------------------------------------------------------
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

' Replace NameOfTableOrQuery with the real name of the table or query,
' replace NameOfForm with the real name of the form, and replace
' ADateControlOnForm and AnotherDateControlOnForm with the real names
' of the controls on that form
strSQL = "SELECT q_Belastningsdata.* FROM q_Belastningsdata ;"

strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Replace C:\MyFolderName\MyFileName.xls with the real path and filename for
the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF, "W:\Lysavis\TVC-Eksp\Belastning.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

 

[Michael Aggerholm]

Should note, the worksheet is also named "q_Belastningsdata", and I dont need
any filters, but just the entire query dumped into the sheet.-.
--
Greetings

Michael


"Michael Aggerholm" skrev:

 

[Michael Aggerholm]

Hmm, how is the code activated ?

--
Greetings

Michael


"Ken Snell [MVP]" skrev:

 

[Ken Snell [MVP]]

You need to put this code inside a Function or Sub, and then run the
Function or Sub. This can be done by a Click event of a command button on a
form, or by a macro that calls the Function (note, you cannot call a Sub
from a macro).

 

[Michael Aggerholm]

Now I've got it to work, thanks alot..

One tiny thing, is it possible to write into an existing worksheet and
overwrite current data ?

--
Greetings

Michael


"Ken Snell [MVP]" skrev:

 

[Ken Snell [MVP]]

Yes, if the data are in contiguous cells in the worksheet. If the data begin
in cell A1, just name the query the same name as the worksheet that contains
the data. Otherwise, this article may provide an alternative approach:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Michael Aggerholm]

Hi There..

Here is what've done..

I think I've done what you said, but it still generate new sheets in the
sheet every time I run it..


Michael


Sub skrivXLS()

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT q_Belastningsdata.* FROM q_Belastningsdata ;"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ,
"W:\Lysavis\TVC-Eksp\Belastning.xls", , , yes
' The destination sheet in the workbook is also named "q_Belastningsdata"

dbs.Close
Set dbs = Nothing

End Sub


--
Greetings

Michael


"Ken Snell [MVP]" skrev:

 

[Ken Snell [MVP]]

When it generates a new sheet, what is the name of that new worksheet that
is generated?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/