Outputting to existing XLS

[Jusenkyo]

Hello all

I have a query that I want to export to an existing excel spreadsheet.
The spreadsheet is all formatted and coloured up, and I just want to put in the numbers from this query.

I cant do this with docmd.OutputTo - as it only allows you to create a new spreadsheet or overwrite an existing one.

Anyone got any ideas in VB?
Cheers in advance!
J

 

[PHV]

Why not pull instead of push ?
In Excel, menu Data -> External data -> ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jusenkyo]

I wanted this to be an automated process really. Dont want the user to link to the SQL server themselves.

 

[larsen18]

Hi
You realy want to tell excel what to do take a look at

http://www.mvps.org/access/modules/mdl0006.htm

, here is an example I Use:
***************
Sub sCopyRSToNamedRange()
'Copy records to a named range
'on an existing worksheet on a
'workbook
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim klubb As String
klubb = DLookup("KlubbNavn", "tblKlubbopplysninger")
Dim Inst As String
Inst = Form_frmSeminar.Instruktør
Dim SDAG As Date
SDAG = Form_frmSeminar.SeminarDato
Dim Rapport As String
Dim strWKB_NAME As String
Dim strSHT_NAME As String

Const conMAX_ROWS = 200
Const conRANGE = "Navn"
Rapport = "qryGradering"
strWKB_NAME = CurrentProject.path & "\systemfiler\NTFver2.1.xls"
'strWKB_NAME = "C:\Mine dok\KampanjeDB\Rapportmal\FremdriftsRapport.xls"
strSHT_NAME = "Hovedliste"


Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset(Rapport, dbOpenSnapshot)
With objXL
'.ActiveWindow.SelectedSheets.PrintOut
.Visible = True
Set objWkb = .Workbooks.Open(strWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(strSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = strSHT_NAME
End If
Err.Clear
On Error GoTo 0
'objSht.Range("A1").Value = "Fremdriftsrapport"
objSht.Range(conRANGE).CopyFromRecordset rs, 200
'objSht.Range("G1").Value = Uke

'objSht.Range("A1").Select
objSht.Cells.Select
objSht.Cells.EntireColumn.AutoFit
objXL.Run "Gup"
objWkb.Sheets("Forside").Select
objWkb.Sheets("Forside").Range("B1").Value = klubb
objWkb.Sheets("Forside").Range("B2").Value = SDAG + 1
objWkb.Sheets("Forside").Range("B3").Value = Inst
objWkb.SaveAs CurrentProject.path & "\Rapporter\" & "GraderingRapport-" & SDAG + 1
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
**********