Crystal Reports export to Excel - Loss of Leading Zeros

[Guest]

Crystal Enterprise is the web-based front-end server which displays
stored/linked Crystal reports to my target audience. I use an export
function that sends the report data to a .csv file so my users can open and
manipulate it in Excel. The file I'm trying to create MUST maintain the data
types of the underlying data fields, but currently is not retaining leading
zeros.

Leading zeros in various fields (ID#/SSN, Zip Codes, etc.) are not
recognized and retained by Excel because it seems the incoming field data is
seen as numerical instead of text. Using known pre-Excel formatting tricks
employing various combinations of using an apostrophe before the data,
enclosing the data in quotation marks (before and after), and using space(s)
in combination with the ' or " characters proves ineffective.

I am trying to correctly force-format the data containing leading zeros as
TEXT, BEFORE it reaches Excel, in order to retain these leading zeros.

Please offer assistance or solutions. Your help is most appreciated. Thanks.

 

[Frank Kabel]

Hi
try renaming the *.csv file to *.txt and open this in Excel. The import
wizard should allow you to choose 'Text' for this data column

 

[Guest]

Hi Frank,

Thanks for the help, but that solution isn't what I'm looking for. My users
have to be able to just click the export icon I provide and the exported file
automatically opens in Excel with the leading zeros intact, with no
developer/programmer or user intervention. So, I need the format to BE text
BEFORE it opens in Excel.

Can this be done? Thanks in advance again for any further assistance you
(or anyone reading this post) can offer.

 

[Dave Peterson]

Another option.

Give the users a workbook that imports the workbook.

It can prompt them for the name of the file to bring in. If it ends with .csv,
it could temporarily rename it and then import it. Copy the data to its new
home (and apply tons of other formatting).

Then rename the file back to its original name.

The user won't double click on the file in explorer. But they could double
click on it in the File|open dialog.

Kind of like this:

Option Explicit
Sub testme()

Dim myOrigFileName As Variant
Dim myNewFileName As String
Dim wkbk As Workbook

myOrigFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.csv")

If myOrigFileName = False Then
Exit Sub 'user hit cancel
End If

myNewFileName = myOrigFileName
If LCase(Right(myOrigFileName, 4)) = ".csv" Then
myNewFileName = Left(myOrigFileName, Len(myOrigFileName) - 4) _
& ".importmenow"
Name myOrigFileName As myNewFileName
End If

Workbooks.OpenText Filename:=myNewFileName, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1)

Set wkbk = ActiveWorkbook

ActiveSheet.Copy 'move it to a new workbook

wkbk.Close savechanges:=False

If myNewFileName = myOrigFileName Then
'do nothing--not changed
Else
Name myNewFileName As myOrigFileName
End If

End Sub

This portion will depend on the layout of your .csv file:

Workbooks.OpenText Filename:=myNewFileName, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1)

I'd record a macro when I did it manually to get everything perfect. Then steal
that little portion that does all the work and embed it into this code.

And I'd also record a macro that added headers to the data, did
data|filter|autofilter, set the print settings--anything that needs to be done
each time you open the file.

If you make your macro useful enough, people will want to use it and not want to
do all that other work themselves.

 

[Guest]

Hi Dave.

Thanks for the suggestion, but again, nothing I can use.

I have to be done with formatting or manipulating the file BEFORE my users
ever see the file. Then, all they should have to do it click on the download
link (export icon) to either OPEN it immediately or SAVE it. They cannot and
will not do anything to the file or formatting.

Any other ideas? FRANK? How about you?

 

[Dave Peterson]

Well, you've got bigger trouble. If you were expecting to fix all the formats
(columnwidths,fonts,colors, etc), then you won't be able to do it saving to a
..csv file.

My last thought is for you to do the formatting and save the file as a real .xls
file.


Ok, one more thought: I haven't used CR in a long time, but can't it save .xls
files directly? (I know that the last time I had to use a .xls file created in
CR, it didn't do a very nice job.)