By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Including Combobox Selection into Exported Excel filename - VBA

P: 6
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):

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, _
  2.    acSpreadsheetTypeExcel8, _
  3.    "Query name", _
  4.    "File Path" & _
  5.    Format(Date, "ddmmyyyy") & _
  6.    ".xls" & _
  7.    "[Forms]![Qry001_Main_Form]![Select_Team]"
Any help on this would be appreciated, I cant seem to find anything.

Thanks.
Feb 6 '14 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,221
You have to be sure the ".xls" is the last part.

Jim
Feb 6 '14 #2

P: 6
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.
Feb 10 '14 #3

100+
P: 294
Here is code that refers to a TextBox control.

Expand|Select|Wrap|Line Numbers
  1.             outputFileName = CurrentProject.Path & "\ReportsReportName" & Year(StartDate) & ".xlsx"
  2.             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.
Feb 10 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. Sub zj_excel_sheet2tablet_1()
  2. Dim ZFilename As String 
  3. Dim zqryout As String
  4. ZFilename = "C:\Documents and Settings\All Users\Workbook1.xlsx"
  5. zqryout = "Query1"
  6. DoCmd.TransferSpreadsheet TransferType:=acExport, _
  7.     Spreadsheettype:=acSpreadsheetTypeExcel12Xml, _
  8.     TableName:=zqryout, _
  9.     Filename:=ZFilename, HasFieldNames:=True_
  10.     ',Range:= "B2:J32" 'uncomment if you want to goto a range.
  11. End Sub
  12.  
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:

Expand|Select|Wrap|Line Numbers
  1.  ZFilename = "File Path" & _
  2.    Format(Date, "ddmmyyyy") & _
  3.    ".xls" & _
  4.    "[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)
Expand|Select|Wrap|Line Numbers
  1. "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:
Expand|Select|Wrap|Line Numbers
  1. Sub zj_excel_sheet2tablet_2()
  2. Dim ZFilename As String 
  3. Dim zqryout As String
  4. Dim zcbotext as string
  5. Dim zdate as string
  6. '
  7. 'Get the second column of the selected item in the combox
  8. 'if you have two columns then 1st = 0 most likely bound
  9. 'and you can simply call the dot-value of the control
  10. '2nd = 1 and you either have to set focus or use this trick
  11. zcbotext = Forms.Qry001_Main_Form.Select_Team.Column(1)
  12. '
  13. 'Let's format the date... using something sort friendly:
  14. zdate = format(date(),"yyyymmdd")
  15. '
  16. 'and now add these to the path I used before:
  17. ZFilename = "C:\Documents and Settings\All Users\Workbook1" & _
  18. zcbotext & zdate & ".xlsx"
  19. '
  20. 'name of a stored query. Xfer Mthd will NOT use dynamic
  21. zqryout = "Query1"
  22. '
  23. ' and finally
  24. DoCmd.TransferSpreadsheet TransferType:=acExport, _
  25.     Spreadsheettype:=acSpreadsheetTypeExcel12Xml, _
  26.     TableName:=zqryout, _
  27.     Filename:=ZFilename, HasFieldNames:=True_
  28.     ',Range:= "B2:J32" 'uncomment if you want to goto a range.
  29. End Sub
  30.  
ʕᴥʔ
Feb 11 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.