Exporting From Access to an Existing Excel Sheet.

[Luther1978]

I have a button on my Form in access that allows me to save my query result to an excel file.

I make lots of queries to put into a Workbook to graph at the end.

Is there any way that each query I make could be added to one file as a new worksheet.

Ab better solution still would be that I could click my button in access and the data would be subsequently added to a file as a new worksheet, from a template worksheet I have created so that Formulaes etc are already inplace.

Many Thanks

Martin King

 

[IFELSETHEN]

Hi Martin,
I would think that you can do this with an Excel Macro and call this Macro at the time you save your data to Excel.

John

 

[Luther1978]

would make sense. I'll give it a go. Cheers

 

[Nikita6003]

Try this:

public Sub Write QueryResulToNewExcelSheet(p_sQueryName As String)
    Dim l_appXL As Excel.Application
    Dim l_wkbSheetNames As Workbook
    Dim l_wksSheet As Worksheet
    Dim l_iCol As Integer
    Dim l_lRow As Long
    Dim l_lRecordCount As Long
    
    Dim l_rsRecordset As Recordset
    
    Set l_appXL = CreateObject("Excel.Application")
    l_appXL.Visible = True
    
    Set l_wkbSheetNames = l_appXL.Workbooks.Open("C:\Temp\Test.xls")
    Set l_wksSheet = l_wkbSheetNames.Sheets.Add
    l_wksSheet.Name = "NewSheet" & Format(Now, "yyyymmdd hh:mm:ss")
    Set l_rsRecordset = CurrentDb.OpenRecordset(p_sQueryName, dbOpenDynaset)
    
    'Pick up recordcount - this to use a For l_lRows .. Next rather than a Do .. Until .EOF
    If l_rsRecordset.EOF Then Exit Sub
    l_rsRecordset.MoveLast
    l_lRecordCount
    l_rsRecordset.MoveFirst
    
    For l_lRow = 2 To l_lRecordCount
        For l_iCol = 1 To l_rsRecordset.Fields.Count
            l_wksSheet.Cells(l_lRow, l_iCol) = l_rsRecordset.Fields(l_iCol - 1)
        Next l_iCol
    Next l_lRow
    
    'Release xl objects
    Set l_rsRecordset = Nothing
    Set l_wksSheet = Nothing
    l_wkbSheetNames.Close xlDoNotSaveChanges
    Set l_wkbSheetNames = Nothing
    l_appXL.Quit
    Set l_appXL = Nothing
    
    'Release recordset object
    l_rsRecordset.Close
    Set l_rsRecordset = Nothing
    
End sub

Create a loop where you call this sub for every query you want to run.

HTH

Cheers
Nikki

 

[Luther1978]

Thanks Nikki, Looks promising.