Scientific Notation exporting VBA to .CSV opening in XLS

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello - i have a VBA that will do a set of commands the end of it will export a sheet that has 1 column with a 9 digit letter/number into CSV. Before exporting the 9 digit presents as it should; but when opening in CSV through XLS it converts some of the data that have an "E" in it to scientific notation. Is there anything i can do prior in my VBA to remove this issue? or do i need to use VBA to reopen the CSV, adjust, and save? would rather the first approach seems after.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Hello - i have a VBA that will do a set of commands the end of it will export a sheet that has 1 column with a 9 digit letter/number into CSV. Before exporting the 9 digit presents as it should; but when opening in CSV through XLS it converts some of the data that have an "E" in it to scientific notation. Is there anything i can do prior in my VBA to remove this issue? or do i need to use VBA to reopen the CSV, adjust, and save? would rather the first approach seems after.
There is a good chance that the VBA code is doing what it should. Where the problem probably lies is in re-opening the CSV file in Excel. You see, when you open a CSV file in Excel, Excel will automatically perform the conversions it thinks should happen on the data.

For this reason, if you truly want to see what a CSV file actually looks like, you should not use Excel to view it (because Excel is performing its own automatic conversions on it).
If you truly want to view the contents of CSV file, view it with a Text Editor, like NotePad, WordPad, or your favorite third party Text Editor.
 
Solution

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
There is a good chance that the VBA code is doing what it should. Where the problem probably lies is in re-opening the CSV file in Excel. You see, when you open a CSV file in Excel, Excel will automatically perform the conversions it thinks should happen on the data.

For this reason, if you truly want to see what a CSV file actually looks like, you should not use Excel to view it (because Excel is performing its own automatic conversions on it).
If you truly want to view the contents of CSV file, view it with a Text Editor, like NotePad, WordPad, or your favorite third party Text Editor.
Ok I use the .csv for an upload to another system - opening as XLS as you say will just change display when I open as notepad I do see the field correct so I will move this as a non issue and dont need to change what I am doing.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Ok I use the .csv for an upload to another system - opening as XLS as you say will just change display when I open as notepad I do see the field correct so I will move this as a non issue and dont need to change what I am doing.
Excellent!

Yes, one of my biggest pet peeves is that they set Excel as the default program to open CSV files. It can cause a lot of confusion, as you have seen. I wish they would use a Text Editor program instead. Usually, that default program is one of the first things I change when I get a new computer.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Excellent!

Yes, one of my biggest pet peeves is that they set Excel as the default program to open CSV files. It can cause a lot of confusion, as you have seen. I wish they would use a Text Editor program instead. Usually, that default program is one of the first things I change when I get a new computer.
Thanks for your help and knowledge on this. Very much appreciated
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
You are most welcome.
Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top