using text box inputs to use in your filename

[murray83]

Merry December Everyone

trying to save a file with this

outputFileName = CurrentProject.Path & "\BWS Raw Data " & "txtChooseFrom.Text" & "txtChooseTo.Text" & ".xls"

and the only bit which is leting me down is the txtChooseFrom and ChooseTo which are the dates inputted by user but all it does is put the name txtchoosenfrom and txtchoosento but i just want it to display the date inputted

all help much appreciated

 

[namliam]

try .value instead of .text

 

[murray83]

try .value instead of .text

Gave it a whirl but sorry to say same fault, is it due to the text being entered a date ? so does it need ' instead of "

here is my full code

Private Sub lblRawData_Click() 'query for raw data
DoCmd.SetWarnings False ' removes warnings
DoCmd.OpenQuery "Qry_DeleteFromExtractData", acViewNormal
DoCmd.OpenQuery "Qry_RawData", acViewNormal                'runs the extract data query,  pulls data from main table
DoCmd.OpenTable "tblExtractData", acViewNormal             'shows the table of extracted data

'outputs table to an excel file for adding to an email
Dim outputFileName As String
'outputFileName = CurrentProject.Path & "\BWS Raw Data.xls"
outputFileName = CurrentProject.Path & "\BWS Raw Data " & "txtChooseFrom.value" & "txtChooseTo.value" & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblExtractData", outputFileName, True

DoCmd.SetWarnings True ' turns on warnings
End Sub

also see attached of the outputs, sad face

 

[isladogs]

One issue is the quote marks. Its just treating the contents as a string.
Omitting those, I think it will still fail if your dates look like 10/12/2019 even using .Value (though that can be omitted as its the default). Better to use e.g. 20191210
Suggest you save the two dates are variables e.g. strStartDate=Format(Me.txtChooseFrom,"yyyymmdd")
Then use the two variables in your file name

 

[vba_php]

murray,

I don't know if this can help you or where in the world you're at, but I always store files, sorted by date created and the date created is inserted into the file name like this:

2019.04.02

where the year comes first, then the month then the day. when I get many files they are then sorted in order and easily readable. if your boxes are DATE data type, you can use the functions cstr() and replace() or something like those to transform the date into something that you can recognize like what I do. Just one another tool for you...

also, note that " \ " and "/" are not allowed in file names.

hey colin, interesting note here. when i typed this message for the first time and I included " \ " in quote without the space, it didn't show when the message was posted because PHP apparently recognized it as an escape string!

 

[murray83]

murray,


also, note that " \ " and "/" are not allowed in file names.

hey colin, interesting note here. when i typed this message for the first time and I included " \ " in quote without the space, it didn't show when the message was posted because PHP apparently recognized it as an escape string!

the "" is for the folder not included in the filename what so ever

 

[vba_php]

the "" is for the folder not included in the filename what so ever

i think u misunderstood what i was saying murray. I was joking to isladogs about how the code on this forum runs.

 

[murray83]

One issue is the quote marks. Its just treating the contents as a string.
Omitting those, I think it will still fail if your dates look like 10/12/2019 even using .Value (though that can be omitted as its the default). Better to use e.g. 20191210
Suggest you save the two dates are variables e.g. strStartDate=Format(Me.txtChooseFrom,"yyyymmdd")
Then use the two variables in your file name

isladogs thanks for the idea, guess what it works ( after a fashion see attached for my attempts final good one in lovely green )

here is my finished code

Private Sub lblRawData_Click() 'query for raw data

DoCmd.SetWarnings False ' removes warnings
DoCmd.OpenQuery "Qry_DeleteFromExtractData", acViewNormal  'makes sure the table is empty first
DoCmd.OpenQuery "Qry_RawData", acViewNormal                'runs the extract data query,  pulls data from main table
DoCmd.OpenTable "tblExtractData", acViewNormal             'shows the table of extracted data

'outputs table to an excel file for adding to an email
Dim outputFileName As String
strStartDate = Format(Me.txtChooseFrom, "dd.mm.yyyy") 'for the from date for the file name
strEndDate = Format(Me.txtChooseTo, "dd.mm.yyyy")     'for the to date for the file name

outputFileName = CurrentProject.Path & "\BWS Raw Data " & strStartDate & " To " & strEndDate & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblExtractData", outputFileName, True

DoCmd.SetWarnings True ' turns on warnings

End Sub

 

[isladogs]

Excellent. Glad I could help.
As you've found out, it will allow dots in the date values but slashes would I think have failed
I'd still personally use something like BWSRawData_20191201_20191203.xls but its your app!

 

[murray83]

ok i said it was solved but just one amednum if you dont mind

have email script like so

'email script for email report for BWS Raw Data
Public Sub CreateEmailWithOutlookRawData()

Forms!frmMain.Requery

'bits for the attachment
Dim myPath As String
Dim sTo As String
Dim pDHL As String  'to try and add the dhl logo
Dim pSAIN As String 'to try and add the sainsburys logo

    pDHL = "G:\GENERAL\STOCK\supplier compliance\BWS Supplier Compliance\BWS Compliance DB\SigPictures\dhl.jpg"  'dhl logo path
    pSAIN = "G:\GENERAL\STOCK\supplier compliance\BWS Supplier Compliance\BWS Compliance DB\SigPictures\sainsburys.jpg"  'sainsburys logo path
  

    myPath = "G:\GENERAL\STOCK\supplier compliance\BWS Supplier Compliance\BWS Compliance DB\Raw Data\"
    strReportName = outputFileName
        

    ' Create a new email object
    Set olApp = CreateObject("Outlook.Application")
    Set olMailItem = olApp.createitem(olMailItem)


    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = ""
        .cc = ""
        .Subject = "BWS Raw Data - " & " " & strStartDate & " - " & " " & strEndDate & ""
        .HTMLBody = "<html><body><font face=calibri>Hi All,<br><br>Please find attached BWS Raw Data for <br><br> Kind Regards <br>Kev </html>"
        .Attachments.Add myPath & strReportName ''adds the attacment well it will do
        .Display    ' Displays before sending the message
    End With


    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing
        
End Sub

but the part which i am haing issue with is getting the file created attached would i need to dim the filename or can i call the variable again as at the moment it dosent it says what is shown in the attached

cheers

 

[Gasman]

You are trying to add a file in your folder called "outputFileName", yet in the previous code you create a file with dates in it's name?

Pass in the filename to your sub when you call it.?

 

[murray83]

You are trying to add a file in your folder called "outputFileName", yet in the previous code you create a file with dates in it's name?

Pass in the filename to your sub when you call it.?

yes i know thats what im trying to do but not sure how to call the file name with dates ????

 

[Gasman]

Where are you calling it from?

You need to keep the content of outputfilename in some way?.

Then use with Call CreateEmailWithOutlookRawData(OutputFileName)

and change sub to CreateEmailWithOutlookRawData(OutputFileName As String)

HTH

 

[murray83]

Where are you calling it from?

You need to keep the content of outputfilename in some way?.

Then use with Call CreateEmailWithOutlookRawData(OutputFileName)

and change sub to CreateEmailWithOutlookRawData(OutputFileName As String)

HTH

sorted it with a bit if thinking and trial and error but here is the fix

    myPath = "G:\GENERAL\STOCK\supplier compliance\BWS Supplier Compliance\BWS Compliance DB\Raw Data\"
    'strReportName = "Failure Report.pdf"
    strReportName = "BWS Raw Data " & strStartDate & " To " & strEndDate & ".xls"

promise that's it for this year of access questions

 

[isladogs]

Thought that's what I suggested in post #4

 

[murray83]

Thought that's what I suggested in post #4

i did use as file name but was trying to add it as attachment but all good and everyone is happy