Saveas csv produces different file sizes

[njbeancounter]

Hi all...

So my macro to saveas csv finally works!

However, the reason I need a csv file is to load the data into an ADP system.

The saved file does not load properly, BUT, if the data is copied and pasted to a new file, and saved as a csv file, the import then works.

The one oddity that I can see is that the macro saved file is slightly larger than the re-saved file.

On the surface, both appear to have identical data.

I didnt come this far to have to open the file, copy the data, and save the file again...kinda defeats the purpose, right?

Any suggestions would be appreciated

Steve

 

[Joe4]

First suggestion would be to post your code, so we can see exactly what it is doing.
Without seeing that, we can only guess as to what the problem might be.

 

[njbeancounter]

Joe...Fair point...ty

Sub savesllc()
'
Dim llcname As String

llcname = Sheets("main").Range("llcname")

'copies the range llcems to a new file

Application.Goto reference:="llcems"
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' saves the new file

ChDir "C:\Users\stephenm\Documents\Steve\ADP\EMS"
ActiveWorkbook.SaveAs Filename:=llcname, FileFormat:=xlCSV

'closes current file
ActiveWorkbook.Close

Application.Goto reference:="home"

End Sub

 

[Joe4]

Can you compare the original exported file to the newly copied/saved one to see if there are any differences (I am guessing there will be, as you said the files are not the same size)?
You will want to do this comparison in a Text Editor program (like NotePad, WordPad, or some other Text Editor), and not Excel.

A few of the things to look for is:
1. Has the length of any of the rows changed? If so, what changed? Culprits could be something like leading zeroes from certain fields being dropped, date formats changing, or number formats changing.
2. Do both files end in the same place on the same row? Does one have a carriage return at the end, and the other doesn't?

 

[njbeancounter]

Now I see why you are an MVP Joe...

Opening the files in Notepad revealed leading zeroes in the employee id in one file, but not the other.

I wish I posted here sooner!

Thank you
Steve

 

[Joe4]

You are welcome!

I have had lots of experience working with data files. One of my biggest "pet peeves" is that Microsoft deemed Excel as the default program to open CSV files. This often leads to "bad" results, as opening the CSV file in Excel may actually do some conversions on the data, like dropping zeroes, changing date & numeric formats, etc. If you truly want to see what is contained in a CSV file (without anything being altered along the way), you need to use a Text Editor to view it, and not Excel.

One of the first things I do when I get a new computer is go into the file association settings, and change it so that Excel is not the default program to open CSV files.

 

[njbeancounter]

Any day I learn something is a good day!