user defined Parameter used as part of a filename in Access

Question

I have an access 2007 Database that outputs a report in excel format, the report is dependent on a date parameter that is chosen by the user. This parameter is selected via a textbox (text100) that has a pop up calendar. I would like to use the date in the text box(text100) as part of the filename. I am using the transferspreadsheet method to create the export, However I do not need the column headers. Once the file is created I have the code open the file and delete the headers. Also the current code is using todays date in the filename which is not accurate. The filename needs to reflect the date that was selected by the user in the text box from the pop up calendar

Ok here is the code.

Sub Branch298nohdr()


 Dim Filename As String
 Dim Path As String
 Dim Branch As Integer
 Dim Text100 As Date
 Dim xl


    Branch = "298"
    Path = "Path" & Branch & "\"
    Filename = "Identity Report " & Branch & " " & _
                        Replace(Text100, ":", " ") & ".xls"



If Dir(Path & Filename) <> "" Then

    MsgBox "File has been created already"

If Dir(Path & Filename) <> "" Then

    GoTo 53

End If

Else


    Set xl = CreateObject("excel.application")


    TempVars.Add "branchnum", Branch 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
            "queryname", Path & Filename, False
    xl.workbooks.Open Path & Filename

     With xl

          .Rows("1:1").entirerow.Delete
            .Columns("L:L").select
            .Selection.NumberFormat = "0"
          .range("a1").select
            xl.workbooks(1).Close Savechanges:=True

            xl.Quit
    Set xl = Nothing
53
    MsgBox "Done!"
    End With

    TempVars.Remove "branchnum"
End If

Branch298nohdr_Exit:
    Exit Sub



End Sub

Text 100 is where the user selects a date via a pop up calendar. I would like to use this date as part of the file name. Currently using the text100 as part of the filename it is being referenced as 12:00 am, and then adds this to the file name. I hope this clears up my intention.

Text 100 gets set on the opening form then there are several buttons which allow the user to pick between several branches or all branches.

Answer 1

It's a little tough to determine exactly what your question is, but I think you are asking, "how do I use the contents of a text box as an export file name?" It sounds like somewhere in your code it creates a string for the filename that has & now() tagged on to the end to use the current date in the filename. Can you simply replace the '& now()' with '& textbox.value'?

Answer 2

A JD Long said it is hard to see any question in your posting. Maybe you should edit it again.

But as a general remark you need to escape any special characters that the user entered before you are going to use the input in a file name.

The following reserved characters are not allowed:

< > : " / \ | ? *

For more details on naming files in Windows see: Naming a File or Directory in MSDN.

Answer 3

Well the obvious question is, where does Text100 get first set?

Another style comment, it is better to do

goto ExitSub
'...
ExitSub:

Then your "GoTo 53" - its a little more meaningful.

At that rate, it would be better to move your "Done" message outside of the IF statements, and the TempVars doesn't seem to have a purpose; remove it.

Edit:

I presume if Text100 is a textbox on the form, then the line that reads:

Dim Text100 As Date

is going to override that reference in your code.

If you are referencing that textbox in your code, you need to do it this way:

foo = me.Text100
' or
foo = Forms!FormName.Text100