no not a problem thats fine. thank you for looking into it.. i am still trying to make it work as well (without any luck at this point)...i think i am going to put it down as well and start with freash mind tomorrow...
thank you once again

Hi,
This is a snippit of code that I have for my export to excel, the code forces a insert first row, and then inserts the information I want in the first cell of the first column. It should give you something to work with:
Code:[COLOR=blue]Set[/COLOR] objsheet = ObjExcel.ActiveWorkbook.Worksheets(1) [COLOR=green]'Set the objsheet to active worksheet in the active workbook[/COLOR] [COLOR=blue]With[/COLOR] objsheet [COLOR=green]'With the active worksheet[/COLOR] .Rows("1:1").Font.Bold = [COLOR=blue]True[/COLOR] [COLOR=green]'Set the first row font to bold[/COLOR] .Rows("1:1").Font.Underline = xlUnderlineStyleSingle [COLOR=green]'U[/COLOR][COLOR=green]nderline [/COLOR][COLOR=green]the text in each cell of the first row[/COLOR] .Rows("1:1").Select [COLOR=green]'Insert a new row[/COLOR] ObjExcel.Selection.Insert Shift:=xlDown ObjExcel.Range("A1").Select [COLOR=green]'Select Cell A1[/COLOR] ObjExcel.ActiveCell.FormulaR1C1 = "[COLOR=red]Sky[/COLOR]" [COLOR=green]'Assign the text "Sky [COLOR=red]This is where your query name would go[/COLOR]" to this cell [/COLOR] ObjExcel.Range("B1").Select [COLOR=green]'Select Cell B1[/COLOR] ObjExcel.ActiveCell.FormulaR1C1 = "As At Date :" [COLOR=green]'Assign [/COLOR][COLOR=green]the text "Date:" to this cell[/COLOR] ObjExcel.Range("D1").Select [COLOR=green]'Select Cell D1[/COLOR] ObjExcel.Selection.NumberFormat = "@" [COLOR=blue]With[/COLOR] ObjExcel.Selection .HorizontalAlignment = xlLeft [COLOR=green]'Left align the text[/COLOR] .VerticalAlignment = xlBottom [COLOR=green]'Bottom align the text[/COLOR] .ReadingOrder = xlContext [COLOR=blue]End With[/COLOR]
I hope this helps.
John
Public Function SendTQ2ExcelNameNewSheet(strTQName As String, strSheetName As String, Optional strFileName As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to name your sheet to
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim wks As Object
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo Errors
Set rst = CurrentDb.OpenRecordset(strTQName)
Set ApXL = CreateObject("Excel.Application")
If Dir(strFileName) <> vbNullString Then
Set xlWBk = ApXL.Workbooks.Open(strFileName)
Else
Set xlWBk = ApXL.Workbooks.Add
End If
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets.Add
xlWSh.Name = strSheetName
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
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
xlWSh.Range("A1").EntireRow.Insert
xlWSh.Range("A1").Value = strTQName
If strFileName <> vbNullString Then
If Dir(strFileName) = vbNullString Then
xlWBk.SaveAs strFileName
Else
xlWBk.Save
End If
xlWBk.Close False
End If
ApXL.Quit
Set ApXL = Nothing
rst.Close
Set rst = Nothing
ExitHere:
Exit Function
Errors:
MsgBox "Error " & Err.Number & " - Line # " & Erl & " - " & " (" & Err.Description & ") in procedure SendTQ2ExcelNameNewSheet of Module basExcel", , CurrentDb.Properties("AppTitle")
Resume ExitHere
Resume
End Function
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
For Each qdf In db.QueryDefs
If Right(qdf.Name, 2) = "_r" Then
SendTQ2ExcelNameNewSheet qdf.Name, Left(qdf.Name, 31), "C:\PathAndFileNameHere.xls"
End If
Next