export query to hidden excel worksheet

[allenjasonbrown@gmail.com]

I am hoping this is an easy answer or someone can help with some basic code.



I am trying to export an Access query to an existing excel workbook. The problem is that in the workbook I would like the sheet where I export the data to be hidden when the spreadsheet opens. One of the other sheets is going to reference the data I dump into the hidden sheet?

Can this be done? Any links or examples would be great.

[June7]

What have you tried? Should be possible.

Set the sheet as hidden in the workbook. Try the export. Does it work? What happens?

[allenjasonbrown@gmail.com]

This is the code I am using. I am just trying to export to an existing workbook into a specified sheet.

Public Function ExportToSpecificSheet(QueryName As String, strSheetName As String)

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

On Error GoTo err_handler
strPath = "C:.......\file.xlsx"
Set rst = CurrentDb.OpenRecordset(QueryName)
Set ApXL = CreateObject("Excel.Application")

Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True


Set xlWSh = xlWBk.Worksheets(strSheetName)

xlWSh.Activate
xlWSh.Range("A1").Select

For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst

xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select

ApXL.ActiveSheet.Visible = False

rst.Close
Set rst = Nothing

Exit Function


2 Problems I have. The first is that I have to hide the worksheet after writing the query and second I can only call this function once. If I call it again it opens the workbook again so now I have 2 of the same workbooks running? I have a predefined excel workbook and within the workbook I can create all the hidden worksheets ahead of time.

ANY HELP WOULD BE GREAT

[June7]

Because the Excel object variable is established within the procedure, it is set to nothing when the procedure ends (as are all variables declared in the procedure) but the Excel app is left running (check Windows Task Manager as evidence) so when you rerun procedure, it creates another instance of Excel and the workbook. Must do something with the Excel app (make visible or close) before the procedure ends.