Loop Through Multi-Select Listbox and Transferspreadsheet

rwbrady · Sep 21, 2009

I am trying to write some code that will loop through selected items in a multi-select listbox and perform the transferspreadsheet operation for each item. However I am running into some trouble. Currently, I am getting an "invalid use of null" error.

Here is what I have so far.

Private Sub Command12_Click()

If file_list.ListIndex = -1 Then
'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
'If ListIndex not -1 run transfer spreadsheet
Dim varItem As Variant
Dim FilePath As String
Dim FileSelection As String

For Each varItem In Me.file_list.ItemsSelected
FileSelection = file_list.Value

'DoCmd.TransferSpreadsheet acImport, , _
"tbl_Data", "C:\Documents and Settings\rbrady\Desktop\" & FileSelection, True, "A1:F40"

Next varItem

End If

Any ideas where I am going wrong, or perhaps suggestions on how to structure the code better?

Thanks

missinglinq · Sep 21, 2009

Your problem is here:

FileSelection = file_list.Value

A multi-select Listbox has no Value Property, hence the "invalid use of null" error.

Try replacing the above line with

FileSelection = file_list.ItemData(varItem)

boblarson · Sep 21, 2009

Also, something else to be aware of as you appear to be including a range in your export code. According to the help file:

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

rwbrady · Sep 21, 2009

Thanks so much, the upload code works great.

Thanks!

Loop Through Multi-Select Listbox and Transferspreadsheet

rwbrady

New member

missinglinq

AWF VIP

boblarson

Smeghead

rwbrady

New member

Similar threads

Users who are viewing this thread