Command Button issue on Form

[CoolFactor]

I have a command button on a form that when I click on it, it executes a function that exports data from a query to an new excel file. That parts works fine but what I want is to click on the button again and hope that it executes the same function but gives the new excel workbook a different name instead of the same name as the first time I clicked on the button.

Here is my code:
Option Compare Database
Option Explicit

Private Sub cmdauto_Click()
On Error GoTo err_Handler

MsgBox ExportRequest, vbInformation, "Finished"

exit_Here:
Exit Sub
err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub


Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 33
Const cStartColumn As Byte = 3

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "\ITPS 1.xls"

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add(sTemplate)
Set wks = appExcel.Worksheets(cTabOne)

sSQL = "select * from qry_12"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If Not rst.BOF Then rst.MoveFirst

' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow


Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
Resume exit_Here

End Function

 

[PHV]

Have a look at the SaveAs method of the Excel.workbook object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CoolFactor]

Where can I find that?

 

[PHV]

In the Excel's VBA help.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CoolFactor]

Where would I put this in the code above for my command button though?

 

[PHV]

For me, between the 2 following instructions:
Loop

ExportRequest = "Total of " & lRecords & " rows processed."

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CoolFactor]

Thank you. I having no luck with the help section for the SaveAs method of the Excel.workbook object.

 

[CoolFactor]

Hey PHV,

I appreciate your help and was wondering if you could help with the code I provided above.

I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.

Below you will find the simple query I am trying to export to Excel using a command in an Access Form.

RowID strFY AccountID CostElementWBS CostElementTitle
1 2008 1 7 Integrated Logistics
2 2008 1 7 Integrated Logistics

I want to export the 1st record of this query to an excel workbook in the following way:

In Workheet "Sheet1," I want the data pertaining to field strFY to go to Cell "A1," and then I want the data pertaining to field AccountID to go to Cell "A2."

Then in Worksheet "Sheet2," I want the data pertaining to field CostElementWBS to go to Cell "B1."

FOR THE SECOND RECORD IN THIS TABLE:

I want to export the 2nd record of this table to a new excel workbook in the following way:

In Workheet "Sheet1," I want the data pertaining to field strFY to go to Cell "A1," and then I want the data pertaining to field AccountID to go to Cell "A2." Then in Worksheet "Sheet2," I want the data pertaining to field CostElementWBS to go to Cell "B1."


A step by step process would be much appreciated. I've researching this for a the past 3 days with no luck, so I thank you many times over for your assistance.

Thank you.

 

[PHV]

I suppose you know how to open 2 workbooks in the same Excel application.
Here a starting point:

Do While rst!RowID >= 1 And rst!RowID <= 2
  With appExcel.Workbooks(rst!RowID)
    .Sheets(1).Range("A1") = rst!strFY
    .Sheets(1).Range("A2") = rst!AccountID
    .Sheets(2).Range("B1") = rst!CostElementWBS
  End With
  rst.MoveNext
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CoolFactor]

When I run it I get this error "Subscript out of Range."
Then is VB it points to this line:

With appExcel.Workbooks(rst!RowID)

Also the second record doesn't show up in another worksheet.

Also I replace this code with yours:
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

Your code:
Do While rst!RowID >= 1 And rst!RowID <= 2
With appExcel.Workbooks(rst!RowID)
.Sheets(1).Range("A1") = rst!strFY
.Sheets(1).Range("A2") = rst!AccountID
.Sheets(2).Range("B1") = rst!CostElementWBS
End With
rst.MoveNext
Loop

 

[PHV]

Did you open the 2 workbooks before the loop ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CoolFactor]

No I did not. Is there a way that I don't have to do that, in other words when I push the command button it automatically opens 2 workbooks?

What about that error that I get?

Thank you so much you've been a tremendous help and I really appreciate it. I've spent my whole labor day weekend trying to crack this.

 

[CoolFactor]

Just out of curiosity say if my query had 50 records. Is there a way to so that when you click that command button that each record populates in its own workbook?

Once again I can't thank you enough!