Including Combobox Selection into Exported Excel filename - VBA

[butler808]

Hi, I am currently exporting queries based on a combo box selection to Excel 2010 using Access 2013. The combo box specifies the Team that the query retrieves data for. What I would like to do is include the combo box selection as part of the spreadsheet export i.e. name of file & team (as specified by combobox) & date & .xls. Here is what I have so far (this obviously works up until the combo box bit at the end):

DoCmd.TransferSpreadsheet acExport, _
   acSpreadsheetTypeExcel8, _
   "Query name", _
   "File Path" & _
   Format(Date, "ddmmyyyy") & _
   ".xls" & _
   "[Forms]![Qry001_Main_Form]![Select_Team]"

Any help on this would be appreciated, I cant seem to find anything.

Thanks.

[jimatqsi]

You have to be sure the ".xls" is the last part.

Jim

[butler808]

Hi,

Thanks for your input.

So using your suggestion I can get this filename:
Weekly_KPI_DB_Report_10022014[Forms]![Qry001_Main_Form]![Select_Team]

However, I want to capture the combo box selection in the export file name i.e.
Weekly_KPI_DB_Report_10022014 Nightshift

Is this possible?

Thanks again.

[mcupito]

Here is code that refers to a TextBox control.

            outputFileName = CurrentProject.Path & "\ReportsReportName" & Year(StartDate) & ".xlsx"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ReportName", outputFileName, True

Note the .xlsx is for Excel 2007. (StartDate) is the name of the text box control.

I did not specifically test out your method, however, you are simply concatenating the text of what you typed in the code, as is. What you need to do is capture the .Value (I think) of the combobox and add it as a string to the outputFileName. Until someone else comes along finds a way of doing this, I suggest giving that a try.

[zmbd]

I know that many will simply place the string within the function; however, that makes it very difficult to troubleshoot should you string not resolve properly.
(this is a petpeeve of mine, I really wish that examples in the text and web wouldn't show to build the string within the function)

So I suggest something like this:

Sub zj_excel_sheet2tablet_1()
Dim ZFilename As String 
Dim zqryout As String
ZFilename = "C:\Documents and Settings\All Users\Workbook1.xlsx"
zqryout = "Query1"
DoCmd.TransferSpreadsheet TransferType:=acExport, _
    Spreadsheettype:=acSpreadsheetTypeExcel12Xml, _
    TableName:=zqryout, _
    Filename:=ZFilename, HasFieldNames:=True_
    ',Range:= "B2:J32" 'uncomment if you want to goto a range.
End Sub
 

This is simple, basic, and has been proven to work.

Let's compare this with your code:


Let's start: Line 2, your acSpreadsheetType is not correct for Excel2010 - see my code block

Line 4, starts that file name stuff... So now we can take that stuff and my zfilename and glop it togeither to get
So many errors there.... let's pretend that you had assigned it to a variable thus:

 ZFilename = "File Path" & _
   Format(Date, "ddmmyyyy") & _
   ".xls" & _
   "[Forms]![Qry001_Main_Form]![Select_Team]"

If you place a debug.print zfilename right after this line you're going to get this
(using today's date)

"File Path11022014.xls[Forms]![Qry001_Main_Form]![Select_Team]"

Exactly, as typed above.

and as others have mentioned, line 6, should be at the end and should be xlsx for Excel2010 (however, for the Ac...8 constaint you used the xls is fine... it's just not an excel10 file).

So let's make a few changes:

More than likely, you're not going to get the value shown from [Select_Team] combobox your quotes are wrong, furthermore, you are going to return the bound-column value... ASSUMING, that there are at least two columns being used in the rowsource (usually is if you are using a query/table as source, if not then we'll deal with that)

Let's modify things a tad further using my code block let's add the combo box

further comments within the code:

Sub zj_excel_sheet2tablet_2()
Dim ZFilename As String 
Dim zqryout As String
Dim zcbotext as string
Dim zdate as string
'
'Get the second column of the selected item in the combox
'if you have two columns then 1st = 0 most likely bound
'and you can simply call the dot-value of the control
'2nd = 1 and you either have to set focus or use this trick
zcbotext = Forms.Qry001_Main_Form.Select_Team.Column(1)
'
'Let's format the date... using something sort friendly:
zdate = format(date(),"yyyymmdd")
'
'and now add these to the path I used before:
ZFilename = "C:\Documents and Settings\All Users\Workbook1" & _
zcbotext & zdate & ".xlsx"
'
'name of a stored query. Xfer Mthd will NOT use dynamic
zqryout = "Query1"
'
' and finally
DoCmd.TransferSpreadsheet TransferType:=acExport, _
    Spreadsheettype:=acSpreadsheetTypeExcel12Xml, _
    TableName:=zqryout, _
    Filename:=ZFilename, HasFieldNames:=True_
    ',Range:= "B2:J32" 'uncomment if you want to goto a range.
End Sub
 

ʕ•ᴥ•ʔ