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
> Exporting Listbox Multiple Selections To Excel Workbook, Access 2016    
 
   
JohnMichael72
post Jul 30 2019, 05:01 PM
Post#1



Posts: 5
Joined: 26-July 19



Hello Access Pros!
I am in need of some assistance with a listbox I have created on a form. The listbox contains all of the tables contained in my database, not the content of the tables but the actual table names. I would like the user to be able to select a table or tables from the listbox and via a command button export them to an excel Workbook. I would really LOVE to have a prompt for a save location and name as well instead of hard coding this. The listbox has the multiselect option set to extended. I envision the user selecting the location they want to save the file and then specifying a name for the workbook. The actual sheets contained in the workbook will be the same name as the table that is being exported. I will give the information that I have and forgive me if I miss anything required.

Using Access 2016, same with Excel
Listbox Name: lstTables
Command Button name: btnExportTables
Form Name: frmViewTables

I will greatly appreciate any assistance you can give with this!

John
Go to the top of the page
 
MadPiet
post Jul 30 2019, 05:10 PM
Post#2



Posts: 3,358
Joined: 27-February 09



You'd have to loop through the ItemsSelected collection of the listbox and export them one at a time...

theaccessweb.com/forms/frm0007.htm

Here's the code

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
]
For Each varItem In ctl.ItemsSelected
' you'd use TransferSpreadsheet here...
Next varItem

'******************** Code end ************************
Go to the top of the page
 
JohnMichael72
post Jul 30 2019, 05:18 PM
Post#3



Posts: 5
Joined: 26-July 19



Thank you so much for your help! So I have something close to that that also allows the user to select the location to save; however, I cannot get it to work. I think it is an issue with the DoCmd portion of the code, maybe the table name piece??

CODE
Option Compare Database
Option Explicit

Private Function GetExcelFolder() As String
   Dim fldr As FileDialog
   Dim txtFileName As String

   ' FOLDER PICKER
   Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

   With fldr
      .AllowMultiSelect = False

      ' Set the title of the dialog box.
      .Title = "Please select folder for Excel output."

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
        txtFileName = .SelectedItems(1)
      Else
        MsgBox "No File Picked!", vbExclamation
        txtFileName = ""
      End If
   End With

   ' RETURN FOLDER NAME
   GetExcelFolder = txtFileName
End Function

Private Sub btnExportTables_Click()
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim user_excel_fldr As String

Set frm = Forms!frmViewTables
Set ctl = frm!lstTables
For Each varItm In ctl.ItemsSelected
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, ctl.ItemData(varItm), user_excel_fldr & "\" & "RPD_Cincinnati_Update_" & Format(Now(), "ddmmmyyyy@HHmm") & ".xlsx", True
Next varItm
End Sub
Go to the top of the page
 
WildBird
post Jul 30 2019, 05:42 PM
Post#4


UtterAccess VIP
Posts: 3,627
Joined: 19-August 03
From: Auckland, Little Australia


What is the error you are getting?

Are you stepping through line by line?

user_excel_fldr. Where is this set?








--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Jul 30 2019, 05:43 PM
Post#5


UtterAccess VIP
Posts: 3,627
Joined: 19-August 03
From: Auckland, Little Australia


Maybe try

CODE
Private Sub btnExportTables_Click()
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim user_excel_fldr As String

user_excel_fldr = GetExcelFolder

Set frm = Forms!frmViewTables
Set ctl = frm!lstTables
For Each varItm In ctl.ItemsSelected
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, ctl.ItemData(varItm), user_excel_fldr & "\" & "RPD_Cincinnati_Update_" & Format(Now(), "ddmmmyyyy@HHmm") & ".xlsx", True
Next varItm
End Sub

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
JohnMichael72
post Jul 30 2019, 05:57 PM
Post#6



Posts: 5
Joined: 26-July 19



YOU ARE ABSOLUTELY AWESOME!!! That did it!! Thank you so much for helping me solve this!!

John
Go to the top of the page
 
WildBird
post Jul 30 2019, 07:02 PM
Post#7


UtterAccess VIP
Posts: 3,627
Joined: 19-August 03
From: Auckland, Little Australia


Just eyeballing it, there may be issues, as you are saying you want multiple tables? If so, the filename will overwrite each time. Would need to include the table name in the file name.

Also, if you want to be able to sort the reports alphabetically, use YYYMMDD, not ddmmmyyyy. Irrelevant if you are using part of the table name though.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 12:14 PM