Output Access 2000 to Excel with Automation

[iain2003]

I am desperately searching for a solution to output a set of query results into an existing spreadsheet that has already been formatted whenever I use the getobject function, my results are exported but the spreadsheet does not display the new results only existing results. Any ideas would be greatly appreciated.

 

[KenReay]

Hi

You can open the spread sheet using Automation, and then populate the cells you want to.

Following snipits will point you in general direction, obviously cannot give exact code without more detail:

' declare some Excel objects
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

' Create and Instance of EXCEL, this code creates a spreadsheet, but you can open an existing one
Set xlApp = GetObject(, "Excel.Application&quot
xlApp.DisplayAlerts = False
xlApp.Application.Visible = True
'
xlApp.Workbooks.Add
Set xlBook = xlApp.ActiveWorkbook '.Workbooks.Add
xlBook.SaveAs strFilename
' Clear out all default Worksheets, except 1
For i = 1 To xlApp.Worksheets.Count - 1
xlApp.ActiveWorkbook.Sheets("Sheet" & i).Delete
Next i
'xlApp.Worksheets.Add
Set xlSheet = xlApp.ActiveWorkbook.ActiveSheet 'Set xlSheet = xlBook.Worksheets(1)
xlSheet.NAME = "Summary"

' error trap for above
Select Case Err.Number
Case 432, 429
Set xlApp = CreateObject("Excel.Application&quot
Resume Next

' populate some cells
' Set The Spreadsheet Headings
xlSheet.Cells(1, 1).Value = CompanyName()
xlSheet.Cells(1, 1).Font.Bold = True
xlSheet.Cells(2, 1).Value = ApplicationName()
xlSheet.Cells(2, 1).Font.Bold = True
xlSheet.Cells(3, 1).Value = "Sales, Bankings by Establishment Week " & cboWeekNo & " " & RsE!strCountryDescription
xlSheet.Cells(3, 1).Font.Bold = True

These are just bits of code to illustrate how you do it, not a working example

Good Luck

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK