UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export To Xl - Query, Access 2007    
 
   
pat2mat2003
post Dec 27 2017, 01:56 AM
Post#1



Posts: 355
Joined: 13-January 06



Hi Seasons Greetings!

Every month I have to export to Xl some data produced in Access via query using transfer spreadsheet formula. This is the code I am using.
Private Sub Command4_Click()
On Error Resume Next
Dim form3 As Form
If IsNull(Text0) Or IsNull(Text2) Or Text0 > Text2 Then
MsgBox "Enter both the dates. Fromdate should be earlier than Todate "
Exit Sub
End If
DoCmd.OpenQuery "qryexpxl", acViewNormal
Dim strmonth As String
strmonth = InputBox(" enter month")


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryexpxl", "D:\gst\strmonth.xls , True"
DoCmd.Close acQuery, "qryexpxl"
DoCmd.Close acForm, "form3"

End Sub
When I enter "december" in the input box the query data is exported as strmonth and not december...... Why
Pat

--------------------
patrick mathias
Go to the top of the page
 
PhilS
post Dec 27 2017, 05:24 AM
Post#2



Posts: 451
Joined: 26-May 15
From: The middle of Germany


QUOTE
CODE
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryexpxl", "D:\gst\strmonth.xls , True"
[...]
When I enter "december" in the input box the query data is exported as strmonth and not december...... Why

Because you need to concatenate the hardcoded string of the path with the contents of the variable. So, the variable should not be inside the hardcoded string.
The ", True" at the end shouldn't be part of the string either.
CODE
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryexpxl", "D:\gst\" & strmonth & ".xls" , True


If you want to know more about this topic, you might want to watch my video on string concatenation.

--------------------
New Access 2019 feature annouced: Modern Charts
Go to the top of the page
 
pat2mat2003
post Dec 28 2017, 02:17 AM
Post#3



Posts: 355
Joined: 13-January 06



Hi Phil

Got it .

Thanks.
Pat

--------------------
patrick mathias
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 10:22 PM