How to create a combo box to allow users to select excel worksheet to import to acces

[ili_sophia]

Hi everyone,

I have created a user form to allow users to import the excel worksheet into a specific access table. However, the workbook contains multiple worksheets and i need to create a combo box to allow the users to select the specific worksheet they want to import. Is there a way for me to do this using VBA?

Thank you

 

[sneuberg]

Suggest using a filedialog to do want you want instead of a combobox. I've attached a database that uses one to select an image so that you can see how they work.

 

[ili_sophia]

Suggest using a to do want you want instead of a combobox. I've attached a database that uses one to select an image so that you can see how they work.

But can the file dialog allow me to pick which worksheet i want to upload onto the workbook? Because the workbook that i have consist of worksheets that are the 31 days of the month on each sheet. i would like to be able to select the sheet of the day which i want to upload into access.

This is the code that i have so far:


Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant


Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx"

If diag.Show Then

For Each item In diag.SelectedItems
Me.txtFileName = item
Next
End If


End Sub

Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject


If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFileName, "")) Then

If MsgBox("Do you want to import this file?", vbYesNo) = vbYes Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Dyeing", Me.txtFileName, True
MsgBox "File Imported"

Else
MsgBox "Please select file again"

End If
End If




End Sub

 

[ili_sophia]

Suggest using a to do want you want instead of a combobox. I've attached a database that uses one to select an image so that you can see how they work.

Does the file dialog allow me to select which worksheet in the workbook i want to import into access? Because the current workbook that i have consist of worksheets which are the 31 days in the month on each sheet. I would like to be able to select the sheet of the day to import to access

 

[ili_sophia]

This is the code i have so far:

Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant


Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx"

If diag.Show Then

For Each item In diag.SelectedItems
Me.txtFileName = item
Next
End If


End Sub

Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject


If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFileName, "")) Then

If MsgBox("Do you want to import this file?", vbYesNo) = vbYes Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Dyeing", Me.txtFileName, True
MsgBox "File Imported"

Else
MsgBox "Please select file again"

End If
End If




End Sub

 

[arnelgp]

yes there is a way.
add code to your Textbox AfterUpdate event to get the sheetnames
of the worksheet and put them to your combobox"

' replace yourTextBox with actual textboxname
' replace yourCombobox with actual comboboxname
' before you proceed further
' on design view of your form click on the combobox
' on its Property->Data->Row Source Type: Value List
'
Private Sub yourTextBox_AfterUpdate()
	Dim xlObj As Object
	Dim xlWB As Object
	dim varSheet As Variant
	Dim i As Integer
	'check if workbook really exists
	if dir(Me.yourTextBox)="" Then
		'workbook does not exist, show message
		Msgbox "Workbook " & Me.yourTextbox & " does not exists"
		Exit Sub
	End If
	'workbook exists so get the names of the sheets
	Set xlObj = CreateObject("Excel.Application")
	Set xlWB = xlObj.WorkBooks.Open(Me.yourTextBox)

	'clear the content of your combobox first
	For i = Me.yourCombobox.ListCount-1 To 0 Step -1
		me.yourCombobox.RemoveItem (i)
	Next
	'now add sheet names
	For Each varSheet In xlWB.WorkSheets
		Me.yourCombobox.AddItem varSheet.Name
	Next
	'were done
	Me.yourCombobox.Requery
	me.yourCombobox = ""

	'housekeeping
	set varSheet=Nothing
	xlWB.Close False
	Set xlWB=Nothing
	xlObj.Quit
	set xlObj=Nothing
End Sub

 

[ili_sophia]

Hi, thanks for your code it was able to list the names of the worksheets in the combo box. However, when i click to select one of the worksheet and click on any area of the form, the selection will disappear. why is that so?

 

[arnelgp]

What do u means as in blank, did u set the row source type to value list.

 

[ili_sophia]

yup, i did set it to value list.
But i managed to solve my question already by removing the line
me.yourCombobox = "" in the code

Thanks anyways