Automatically create excell filename via form

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Access 2000. I would like to create a form that would: Enter an office
number through a combobox, such as "764". Next enter a beginning date in a
textbox, then enter an ending date in the next texbox. Click on a cmd
button to run a macro that exports a query to an excel file. I know how to
do this. What I would like is to click the cmd button and have the excel
file automatically named such as: 764_1-1-07to1-7-07 Which includes
the office number in the beginning and the beginning and ending dates. Can
this be done? Thanks in advance, Randy.
 
Firstly, if you have an excel question you really should post it in an excel
forum.

As for the question itself, try something like

Function GetExcel()
' Original Author: Ken Puls
' Original Source: http://www.excelguru.ca/node/10#Excel
' Modified by: Daniel P for the purposes of a Access Forum Question

' Bind to an existing or created instance of Microsoft Excel
Dim objApp As Object
Dim strFileName As String 'Name to save the excel workbook as
Dim strPath As String 'Location where to save the excel workbook
Dim strDept As String 'Dept Number
Dim dtStart As Variant 'Start Date
Dim dtEnd As Variant 'End Date

strDept = Me.DeptControl
dtStart = Me.StartDateControl
dtEnd = Me.EndDateControl
strFileName = strDept & "_" & dtStart & "to" & dtEnd & ".xls"
strPath = "" 'Specify your path where to save the file

strFileName
'Attempt to bind to an open instance
On Error Resume Next
Set objApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
'Could not get instance, so create a new one
Err.Clear
On Error GoTo ErrHandler
Set objApp = CreateObject("Excel.Application")
With objApp
.Visible = True
.Workbooks.Add
End With
Else
'Bound to instance, activate error handling
On Error GoTo ErrHandler
End If

'Add some text to the document
With objApp.ActiveWorkbook
'.Worksheets.Add
.Worksheets("Sheet1").Range("A1") = "Hello!"
.Saveas (strPath & strFileName) 'Saveas a given filename and path
End With

objApp.ActiveWorkbook.Close
objApp.Quit

ErrHandler:
'Release the object and resume normal error handling
Set objApp = Nothing
On Error GoTo 0
End Function
 
Back
Top