[RESOLVED] Export ListBox data to Excel...

[ruslannurijev]

Hi!

Office 2000 Access

I have a listbox on my form with 6 columns in it and some rows.
My purpose is to export data to Excel from that listbox and i can't get it done.

I have tried this:

Private Sub ReportToExcel_Click()
'EXPORT to EXCEL
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSh = xlApp.Workbooks(1).Worksheets(1)
For i = 1 To ReportRecordList.ListCount
    xlSh.Cells(i, 1).Value = ReportRecordList.ItemData(i - 1)
Next


Set xlSh = Nothing
Set xlApp = Nothing
End Sub

But this code returns only 1 column, Header including.
I can't understand what should i do with "i" to start pulling data from column 2 and so on...


Thanks in advance!
Ruslan.

[westconn1]

you need to loop through the columns as well, something like

For i = 1 To ReportRecordList.ListCount
  for j = ReportRecordList.columncount
    xlSh.Cells(i, j).Value = ReportRecordList.list(i - 1, j)
  next
Next

i did this in excel listbox so might not be quite the same for access listbox

[ruslannurijev]

Thanks for the reply!

You are right, the ListBox in Excel and Listbox in Access is not the same.
In Access there is no such property like ".list", only ListCount or ListIndex DataItem.

My code would be useful if i'd want to export first column, but i need to get all the data from list box.
At the moment i do not know how to show the programm that i want value from ListBox.Column(1) or ListBox.Column(2) ListBox.Column(3) and so on...

Any ideas?

Thanks,
Ruslan.

[westconn1]

try the help for access vba

[ruslannurijev]

Hi!

I have managed to get all the data from ListBox without using ".List" property.
After hours of studing ListBox and figuering out why my code pulls only first column from the listbox i came to conclusion that it is the BoundColumn that i must use.

So i loop the BoundColumn value. But at the end of the loop i must manually set BoundColumn value to 1, couse when the export is finished the BoundColumn has value 7(this is in my case, couse i have 6 columns). If i wont set value to 1 then the next time i will get a blank Excel Worksheet, that is bacause BoundColumn still holds value 7 and there is no column 7 in my ListBox.

Code follows:

Private Sub ReportToExcel_Click()
'EXPORT to EXCEL
Dim myExApp As Excel.Application    'variable for Excel App
Dim myExSheet As Excel.Worksheet    'variable for Excel Sheet
Dim i As Long                       'variable for ColumnCount
Dim j As Long                       'variable for ListCount
Set myExApp = New Excel.Application

myExApp.Visible = True              'Sets Excel visible
myExApp.Workbooks.Add               'Add a new Workbook
Set myExSheet = myExApp.Workbooks(1).Worksheets(1)

For i = 1 To ReportRecordList.ColumnCount   'Counter for ColumnCount
    ReportRecordList.BoundColumn = ReportRecordList.BoundColumn + 1 'Setting counter for BoundColumn
    For j = 1 To ReportRecordList.ListCount 'Counter for ListCount
        myExSheet.Cells(j , i) = ReportRecordList.ItemData(j - 1)    'Insert ItemData into Excel Worksheet
    Next j  'Iterating through ListCount
Next i  'Iterating through ColumnCount
ReportRecordList.BoundColumn = 1    'Setting BoundColumn to original 1

Set myExSheet = Nothing 'Release Worksheet
Set myExApp = Nothing   'Release Excel Application

End Sub

Thanks for trying to help, i hope that this Thread will help others to solve same problems.

Best Regards,
Ruslan.

[uronmapu]

Hi!

I have managed to get all the data from ListBox without using ".List" property.
After hours of studing ListBox and figuering out why my code pulls only first column from the listbox i came to conclusion that it is the BoundColumn that i must use.

So i loop the BoundColumn value. But at the end of the loop i must manually set BoundColumn value to 1, couse when the export is finished the BoundColumn has value 7(this is in my case, couse i have 6 columns). If i wont set value to 1 then the next time i will get a blank Excel Worksheet, that is bacause BoundColumn still holds value 7 and there is no column 7 in my ListBox.

Code follows:

Private Sub ReportToExcel_Click()
'EXPORT to EXCEL
Dim myExApp As Excel.Application    'variable for Excel App
Dim myExSheet As Excel.Worksheet    'variable for Excel Sheet
Dim i As Long                       'variable for ColumnCount
Dim j As Long                       'variable for ListCount
Set myExApp = New Excel.Application

myExApp.Visible = True              'Sets Excel visible
myExApp.Workbooks.Add               'Add a new Workbook
Set myExSheet = myExApp.Workbooks(1).Worksheets(1)

For i = 1 To ReportRecordList.ColumnCount   'Counter for ColumnCount
    ReportRecordList.BoundColumn = ReportRecordList.BoundColumn + 1 'Setting counter for BoundColumn
    For j = 1 To ReportRecordList.ListCount 'Counter for ListCount
        myExSheet.Cells(j , i) = ReportRecordList.ItemData(j - 1)    'Insert ItemData into Excel Worksheet
    Next j  'Iterating through ListCount
Next i  'Iterating through ColumnCount
ReportRecordList.BoundColumn = 1    'Setting BoundColumn to original 1

Set myExSheet = Nothing 'Release Worksheet
Set myExApp = Nothing   'Release Excel Application

End Sub

Thanks for trying to help, i hope that this Thread will help others to solve same problems.

Best Regards,
Ruslan.

This code is fine but how to I can export with name field ???

Many Thanks

[uronmapu]

and how to I can import from excel to Table ??

[uronmapu]

This code is fine but how to I can export with name field ???

Many Thanks

Done: In Listbox, Column Heads select yes

[kriswork]

Hi,

An update to this thread for Access 2007 and no need to change bound column:

Dim appExcel As Excel.Application
Dim wksData As Excel.Worksheet
Dim intColumn_Index As Integer
Dim intRow_Index As Integer

Set appExcel = New Excel.Application
appExcel.Workbooks.Add

Set wksData = appExcel.Workbooks(1).Worksheets(1)

For intRow_Index = 0 To lstMissing_User_Fields.ListCount

    For intColumn_Index = 1 To lstMissing_User_Fields.ColumnCount
        wksData.Cells(intRow_Index + 1, intColumn_Index).Value = lstMissing_User_Fields.Column(intColumn_Index, intRow_Index)
    Next intColumn_Index

Next intRow_Index

appExcel.Visible = True

Set wksData = Nothing
Set appExcel = Nothing

Note that intRow_Index starts at zero to get column headers which have been turned on.

Kristian

[lktodd]

Hi,

An update to this thread for Access 2007 and no need to change bound column:

Dim appExcel As Excel.Application
Dim wksData As Excel.Worksheet
Dim intColumn_Index As Integer
Dim intRow_Index As Integer

Set appExcel = New Excel.Application
appExcel.Workbooks.Add

Set wksData = appExcel.Workbooks(1).Worksheets(1)

For intRow_Index = 0 To lstMissing_User_Fields.ListCount

    For intColumn_Index = 1 To lstMissing_User_Fields.ColumnCount
        wksData.Cells(intRow_Index + 1, intColumn_Index).Value = lstMissing_User_Fields.Column(intColumn_Index, intRow_Index)
    Next intColumn_Index

Next intRow_Index

appExcel.Visible = True

Set wksData = Nothing
Set appExcel = Nothing

Note that intRow_Index starts at zero to get column headers which have been turned on.

Kristian

I know some time has passed, but I'm trying to use this. The only problem I am having is that it is skipping outputting the first column of data. I'm using Access 2010. Ideas?

[westconn1]

For intColumn_Index = 1 To lstMissing_User_Fields.ColumnCount

index of first column should be 0 in listbox, but 1 in excel worksheet

try starting from columnindex = 0 but add 1 to the cell column

[lktodd]

Yep, that is exactly what I did and it worked. Thanks.

[lktodd]

I have a followup question to this thread. The export from the listbox is working great - THANKS. However, I would like to add a row in Excel before the data starts that shows the parameters that are in a label in Access. Any idea where I would insert this? I'm really just a Google programmer. I tried to add something before the For loop, but...

[lktodd]

Actually, I solved my own problem. I just put the label values before the For Loop and changed the intRow_Index to start lower in the spreadsheet!