Hello,
I am using this code, to export data from a list box to excel.
It works great but the only problem is that it does not keep the last column formatting. In my db it's a text (can contain numbers and letters). When I export I get strange numbers like 4564.15+65.
I was wandering if is possible to change the formatting of the excel column after data is imported. I know I should write something like:
Columns("L:L").Select
Selection.NumberFormat = "0"
but not sure where to place the code.
The code to export is the following:
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 lstBanks.ListCount - 1
For intColumn_Index = 0 To lstBanks.ColumnCount - 1
wksData.Cells(intRow_Index + 1, intColumn_Index + 1).Value = lstBanks.Column(intColumn_Index, intRow_Index)
Next intColumn_Index
Next intRow_Index
MsgBox "Your selection has been exported to Excel. Click OK to open.", vbInformation, "Search by Bank data"
appExcel.Visible = True
Set wksData = Nothing
Set appExcel = Nothing
Thank you for any help.
I am using this code, to export data from a list box to excel.
It works great but the only problem is that it does not keep the last column formatting. In my db it's a text (can contain numbers and letters). When I export I get strange numbers like 4564.15+65.
I was wandering if is possible to change the formatting of the excel column after data is imported. I know I should write something like:
Columns("L:L").Select
Selection.NumberFormat = "0"
but not sure where to place the code.
The code to export is the following:
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 lstBanks.ListCount - 1
For intColumn_Index = 0 To lstBanks.ColumnCount - 1
wksData.Cells(intRow_Index + 1, intColumn_Index + 1).Value = lstBanks.Column(intColumn_Index, intRow_Index)
Next intColumn_Index
Next intRow_Index
MsgBox "Your selection has been exported to Excel. Click OK to open.", vbInformation, "Search by Bank data"
appExcel.Visible = True
Set wksData = Nothing
Set appExcel = Nothing
Thank you for any help.