Button to export a form and view it in excel

[Bucephalus]

Hi,

is there an option to add a button on a form so that if i click the button all the values displaying on the form will be exported and viewed/edited in excel.I don't want to export the file to my drive also.
i knew the "Tools -> Office Links -> Analyze it with Excel" option.
The problem is i'm sharing the file with my clients,some may not know the ''analyze it with excel" option.so is there really a way to add a button and perform the above action?


Thanks in advance.

 

[speakers_86]

Look into exporting the query to excel. Bob Larson has a great function on his website.

 

[prabhus]

Hi Speakers 86,

I looked in to Bob Larson site and i was trying to use this code for Export A Table Or Query To Excel - But i am facing a problem in linking his code to my button in the form. Could you please help me to fix it?

My code to call Bon's code from my form.

Private Sub Command0_Click()
Call SendTQ2Excel
End Sub

When i run this, i get Compile Error: Argument not optional message how to fix it.

Here is Bob query.

Public Function SendTQ2Excel(MHBTimeSO As String, Optional HBTime As String)
' MHBTimeSO is the name of the table or query you want to send to Excel
' HBTime is the name of the sheet you want to name it to


Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

On Error GoTo err_handler

Set rst = CurrentDb.OpenRecordset(MHBTimeSO)

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(HBTime) > 0 Then
xlWSh.Name = Left(HBTime, 34)
End If

xlWSh.Activate
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

rst.Close
Set rst = Nothing

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

 

[AlexHedley]

The function starts like

Public Function SendTQ2Excel(MHBTimeSO As String, Optional HBTime As String) ...

The values between the brackets ( ) are arguments:

MHBTimeSO As String
Optional HBTime As String

The error messages is saying:
Compile Error: Argument not optional message

Because you aren't passing the function the argument it requires:

Private Sub Command0_Click()
    Call SendTQ2Excel
End Sub

You need to pass 'SendTQ2Excel' a string 'MHBTimeSO' which is the table/query you wish to export to excel.

Try

Private Sub Command0_Click()
    Call SendTQ2Excel "tblName"
End Sub

Replace tblName with the name of your table or query.

 

[prabhus]

Thank You ALex,

But i am receiving a error message

Compile error
Expected: End of Statement

Sorry for my ignorance, i am copletely new to VB

this is my code

Private Sub Command19_Click()
Call SendTQ2Excel "MHBTimeSO"
End Sub

 

[Alansidman]

Try wrapping your table name in parens.

 

[prabhus]

No, i still get the same error...:banghead:

 

[AlexHedley]

Is the name of your Table/Query "MHBTimeSO"?

Private Sub Command19_Click()
Call SendTQ2Excel "MHBTimeSO"
End Sub

When that error appears and you press Debug, which line is highlighted in yellow in the vba window?

As Alansidman suggested did you try:

Call SendTQ2Excel("MHBTimeSO")