using text box inputs to use in your filename (1 Viewer)

murray83

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
Merry December Everyone

trying to save a file with this

Code:
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
 

murray83

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
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

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
 

Attachments

  • nope sorry.png
    nope sorry.png
    2.5 KB · Views: 71

isladogs

MVP / VIP
Local time
Today, 06:06
Joined
Jan 14, 2017
Messages
18,186
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

Forum Troll
Local time
Today, 01:06
Joined
Oct 6, 2019
Messages
2,884
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:
Code:
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! :p
 

murray83

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
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! :p

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

vba_php

Forum Troll
Local time
Today, 01:06
Joined
Oct 6, 2019
Messages
2,884
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

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
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

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
 

Attachments

  • yesssss.png
    yesssss.png
    11 KB · Views: 70

isladogs

MVP / VIP
Local time
Today, 06:06
Joined
Jan 14, 2017
Messages
18,186
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

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
ok i said it was solved but just one amednum if you dont mind

have email script like so

Code:
'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
 

Attachments

  • why.png
    why.png
    24.2 KB · Views: 68

Gasman

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
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

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
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

Enthusiastic Amateur
Local time
Today, 06:06
Joined
Sep 21, 2011
Messages
14,048
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

Games Collector
Local time
Today, 06:06
Joined
Mar 31, 2017
Messages
728
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

Code:
    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
 

Users who are viewing this thread

Top Bottom