How to open an excel template via Access

  • Thread starter Thread starter confused
  • Start date Start date
C

confused

I do not know how to write the code to open an excel template.
I get this message " Query must have at least on destination field"
This is what I have so far:
With oExcel
If Not .OpenTemplate(gstrMainPath & gstrARStatusTemplate) Then
MsgBox "Missing Template " & gstrMainPath &
gstrEmpHrsbyCatTemplate, _
vbOKOnly + vbCritical, "Error"
GoTo MyExit
End If

.ActivateSheet "Sheet1"
.Home
'.Show
'set up collection of matters

Do While Not rsQ.EOF
If r = 0 Then
.InsertRange "Date", Format(Now, "mm/dd/yyyy"), False
'
Else
.InsertOffset r, 0, Trim(rsQ!Emp_no), False
End If

.InsertOffset r, 0, rsQ!Emp_no, False
.InsertOffset r, 1, rsQ!Name, False
.InsertOffset r, 2, rsQ!Hours, False
.InsertOffset r, 3, rsQ!Sick, False
.InsertOffset r, 4, rsQ!Vac, False
.InsertOffset r, 5, rsQ!Personal, False
.InsertOffset r, 6, rsQ!Holiday, False
.InsertOffset r, 7, rsQ!Other, False
.InsertOffset r, 8, rsQ!STD, False
r = r + 1
rsQ.MoveNext
Loop

I looked at sample code to find this much.
 
I do not know how to write the code to open an excel template.
I get this message " Query must have at least on destination field"
This is what I have so far:
With oExcel
    If Not .OpenTemplate(gstrMainPath & gstrARStatusTemplate) Then
      MsgBox "Missing Template " & gstrMainPath &
gstrEmpHrsbyCatTemplate, _
        vbOKOnly + vbCritical, "Error"
      GoTo MyExit
    End If

  .ActivateSheet "Sheet1"
  .Home
  '.Show
  'set up collection of matters

  Do While Not rsQ.EOF
  If r = 0 Then
      .InsertRange "Date", Format(Now, "mm/dd/yyyy"), False
'
    Else
      .InsertOffset r, 0, Trim(rsQ!Emp_no), False
    End If

    .InsertOffset r, 0, rsQ!Emp_no, False
    .InsertOffset r, 1, rsQ!Name, False
    .InsertOffset r, 2, rsQ!Hours, False
    .InsertOffset r, 3, rsQ!Sick, False
    .InsertOffset r, 4, rsQ!Vac, False
    .InsertOffset r, 5, rsQ!Personal, False
    .InsertOffset r, 6, rsQ!Holiday, False
    .InsertOffset r, 7, rsQ!Other, False
    .InsertOffset r, 8, rsQ!STD, False
     r = r + 1
    rsQ.MoveNext
  Loop

I looked at sample code to find this much.
Click to expand...

I figured out my problem; everything is finally working.
 
Why take the long way when you can just export a query or table to excel.
Than you also have the choice to open it immediatly or leave it closed.
If the files doesn't exist there are 2 methods:

DoCmd.OutputTo
or
DoCmd.TransferSpreadsheet

GL,
Cloggy
 
Back
Top