Export to Excel - Colum formatting (1 Viewer)

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:13
Joined
May 7, 2009
Messages
19,169
...
...
Next intColumn_Index
Next intRow_Index

wksData.Range("A:" & Chr(65+Ubound(lstBanks)).Select
appExcel.Selection.NumberFormat = "@"


MsgBox "Your selection has been exported to Excel. Click OK to open.", vbInformation, "Search by Bank data"
 

strive4peace

AWF VIP
Local time
Today, 00:13
Joined
Apr 3, 2020
Messages
1,003
also in the example showing VBA that I linked you to, @JPR, you can see I used CopyFromRecordset instead of writing each cell -- much faster!

you can use Me.lstBanks.Recordset
 

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
Hello Arnel,
the line
wksData.Range("A:" & Chr(65+Ubound(lstBanks)).Select

shows an error. Some missing bracket?
Thank you
 

strive4peace

AWF VIP
Local time
Today, 00:13
Joined
Apr 3, 2020
Messages
1,003
you don't have to select a column to set its format -- take a look at the VBA in the link I gave you -- everything is commented to understand what each line does

The reason it is showing the number in scientific notation is because the column isn't wide enough ... and 10^65 is a big number! So you would want it to be text. However, to keep Excel from converting it to a number before you have a chance to set it to text, you can preface the value with a single quote (that won't show, in Excel) and means the value is text, not a number or date

'999999999999999999999999999999999999999999999999999999999999999
 
Last edited:

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
Sorry Crystal but cannot open the Bas file due to restrictions on my PC.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:13
Joined
May 7, 2009
Messages
19,169
wksData.Range("A:" & Chr(65+Ubound(lstBanks))).Select
 

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
Thanks Arnel,
this time I get an error on the UBound. When I add the additional bracket, additional spaces are added. Sorry for not explaining myself correctly.

wksData.Range("A:" & Chr(65 + UBound(lstBanks))).Select
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:13
Joined
May 7, 2009
Messages
19,169
The code is meant to be added to the code you posted and you have lstbanks on your code.
 

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
thanks Arnel, sorry a bit confused. This is how I have my code:

Next intColumn_Index
Next intRow_Index

wksData.Range("A:" & Chr(65 + UBound(lstBanks))).Select
appExcel.Selection.NumberFormat = "@"

MsgBox "Your selection has been exported to Excel. Click OK to open.", vbInformation, "FindMe 2020 - Search by Bank Menu"
 

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
Sorry friends if I am bothering you on this but I am almost near to the solution. I have been lookin at all your suggestions and without having to do much changes I have used Arnel's code and chaged it as follows:

appExcel.Cells.Select
appExcel.Cells.EntireColumn.AutoFit

wksData.Range("L:L").Select
appExcel.Selection.NumberFormat = "@"

This allows to export to excel and keep almost all the formatting. Unfortunately if records starts with zeros in column L it gest removed. Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:13
Joined
Sep 21, 2011
Messages
14,048
This allows to export to excel and keep almost all the formatting. Unfortunately if records starts with zeros in column L it gest removed. Thank you
So why do you not use a format that will not do that.? :confused:

Also @ as format does not remove leading zeroes for me?
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:13
Joined
Mar 14, 2017
Messages
8,738
Jumping in late, but one tip - you have to format cells as text before you place the data there. Placing it as ############## and then formatting as text usually won't just automagically remove the exponentiation. Without jumping through more hoops.
Formatting as @ is correct IMO, but do it before placing data there..
Did a lot of this with a former company and long account numbers that looked like numbers but should be text.
 

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
This is also what is confusing me. Unfortunately my knowledge is quite poor in programming.
I think a bit more info will help. Data that I export from my lstbox to excel which is not formatting correctly is text.
Examples are: 0123456789 or 12121212CC222, etc,. This meand that although has digits I treat them as text as I do not need to run calculations etc.
When I export, in the L column I get 123456789.

Will appreciate any additional help
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:13
Joined
Mar 14, 2017
Messages
8,738
So one thing you can try is to export them with a single quote (apostrophe) in front of them. 'value
Excel may preserve them as text in this case.
Another thing is to format the destination excel worksheet as text BEFORE exporting them..
 

JPR

Registered User.
Local time
Yesterday, 22:13
Joined
Jan 23, 2009
Messages
192
Solved. As recommended I wrote the format portion of the code before the export. Working great:

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

Set wksData = appExcel.Workbooks(1).Worksheets(1)
wksData.Range("L:L").Select
appExcel.Selection.NumberFormat = "@"

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

appExcel.Cells.Select
appExcel.Cells.EntireColumn.AutoFit


MsgBox "Your selection has been exported to Excel. Click OK to open.", vbInformation, "Search Menu"
 

Users who are viewing this thread

Top Bottom