export from multiple (or one grouped) crystal reports into an excel file on separate sheets 1

[jchewsmith]

I am trying to find a way to export from a crystal report (or several reports) into an excel file with each report or subreport showing on a separate sheet.

 

[SkipVought]

Hi,

As was stated to you in your original thread in a CR forum, you have no way to accomplish what you want from CR alone. Since you need all your reports imported into one Excel workbook, an Excel VBA solution may be your best option.

From CR, you need to EXPORT your reports into one folder. Then, you can have a workbook that contains a VBA procedure that will 1) loop through all the files in a folder that you will designate, 2) IMPORT the data from each file into a new sheet.

Here's the code to accomplish and attached is a workbook...

Sub IMPORT_fromFolder()
'SkilVought 2018 Jun 26
'run in Excel
    Dim oFSO As Object, oFile As Object, sPath As String, sDB As String
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    sPath = "C:\Users\Skip\Documents\TT\Test_CSV"

    For Each oFile In oFSO.GetFolder(sPath).Files
        With oFile
            'file name
            sDB = oFile.Name
            'add a sheet for this file
            ThisWorkbook.Worksheets.Add
            'import
            With ActiveSheet
                .Name = Split(sDB, ".")(0)
                With .QueryTables.Add( _
                    Connection:="TEXT;" & sPath & "\" & sDB, _
                    Destination:=Range("$A$1"))
                    .Name = Split(sDB, ".")(0)
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = False
                    .RefreshStyle = xlInsertDeleteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .TextFilePromptOnRefresh = False
                    .TextFilePlatform = 437
                    .TextFileStartRow = 1
                    .TextFileParseType = xlDelimited
                    .TextFileTextQualifier = xlTextQualifierDoubleQuote
                    .TextFileConsecutiveDelimiter = False
                    .TextFileTabDelimiter = True
                    .TextFileSemicolonDelimiter = False
                    .TextFileCommaDelimiter = True
                    .TextFileSpaceDelimiter = False
                    .TextFileColumnDataTypes = Array(1, 1)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh BackgroundQuery:=False
                End With
            End With
        End With
    Next
End Sub

BTW, this assumes that your report files are .csv text files.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jchewsmith]

Skip,

I added this code to the VBA Project Module in excel, changed the sPath to = the folder that I created with my 3 files I want to combine into an excel workbook but I get the following error:

Run-time error '1004':

You typed an invalid name for a sheet or chart. make sure that:
The name that you type does ot exceed 31 characters.
The name does not contain any of the following characters: :\/?*[ or ]
You did not leave the name blank

Then when I debug it highlights the .Name = Split(sDB, ".") (0)

Am I supposed to update anything besides the folder name in this code?

 

[SkipVought]

What are your file names?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jchewsmith]

My folder is: C:\users\jsmith\CRtoExcel
My files are: Test1.csv, Test2.csv, Test3.csv

 

[SkipVought]

What's happening is that there is some illegal character from the file name that cannot be used in a sheet name.

What you posted as your file names would not result in that error message.

I have no idea!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Ok. Run the procedure to the error.

Hit Debug.

Use the Watch Window to inspect sDB.  faq707-4594

What is the Value in sDB?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jchewsmith]

Skip,

I got it to work!

Thank You Very Much.

 

[Andrzejek]

So what was the issue? Could you share it with everyone?
Others may benefit with your solution...


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

For the benefit of all, please explain what you did that you “got it to work.”

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jchewsmith]

Sorry I just saw this.

I am not exactly sure what did it, but I opened a new workbook and copied the code into the VB module, changed it to my directories/folder and ran it and it worked.

I think it was just something to do with my company trying to block the content of the workbook that I downloaded from you.