Export as csv or txt file using macro

system · November 8, 2002, 8:21pm

Hi, Dave:

Thank you so much. If I tried to change ExportAsCSV, it prompts me to debug.

Ideally, it will be great if I can get the CSV file, cause it is comma delimited. I assume it should also be all right if I use ExportAsCSV, correct?

Thanks a lot.

Lilly

Lilly J (BOB member since 2002-08-22)

system · November 8, 2002, 8:39pm

Only if - as I mentioned above - you want the results of the query, and not the output from the report. In that case, yes. But you don’t work with a document or report, you work with a data provider.

There is no ExportAsCSV function call.

Sub ExportCSV()

    Dim bodoc As busobj.Document
    Dim boDP As busobj.DataProvider
    
    Dim strOutputFileName As String
    
    Set bodoc = ActiveDocument
    Set boDP = bodoc.DataProviders.Item(1)
    
    strOutputFileName = "C:\CSVOutput.csv"
    
    intReturnCode = boDP.ConvertTo(boExpAsciiCSV, 1, strOutputFileName)
    
End Sub

There is no way to export (save as) a report as a CSV directly.

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · November 11, 2002, 3:58am

Dave:

Thank you so much for your insight and explaination. I got it now.

Lilly

Lilly J (BOB member since 2002-08-22)

system · January 8, 2003, 5:16pm

I have a document that contains 4 reports. The first 3 should be saved to three different text files and the 4th should be saved as a pdf? How do I change the code that takes the active document and saves it as text, to make it do what I need it to do (will be attending the macro class in March).

Tammy (BOB member since 2002-11-12)

system · January 8, 2003, 5:43pm

Hi Tammy,

One way of doing it is, before saving it as PDF, delete the first three tabs and use the “ExportAsPDF”. It will save only the remaining tab page as PDF. And, Don’t save your report at the end.


doc.Reports.Item(1).Delete
doc.Reports.Item(2).Delete
doc.Reports.Item(3).Delete
doc.ExportAsPDF (Filename)

Ravi Amara (BOB member since 2002-10-02)

system · January 8, 2003, 6:28pm

Why go thru the process of deleting the reports?? If you are sure there will always be 4 reports then just use the following.

Sub exporting()

ActiveDocument.Reports(1).ExportAsText ("C:\" &amp; ActiveDocument.Reports(1).Name)
ActiveDocument.Reports(2).ExportAsText ("C:\" &amp; ActiveDocument.Reports(2).Name)
ActiveDocument.Reports(3).ExportAsText ("C:\" &amp; ActiveDocument.Reports(3).Name)
ActiveDocument.Reports(4).ExportAsPDF ("C:\" &amp; ActiveDocument.Reports(4).Name)

End Sub

Also look here for an add-in that does something similar.

avaksi (BOB member since 2002-08-22)

system · January 8, 2003, 6:50pm

Oh yeah!!! It worked, thanks so much!

Tammy

Tammy (BOB member since 2002-11-12)

system · April 3, 2003, 11:42pm

Hi

I am trying to Export the report and not the data provider. Can anyone tell me how can I do this using VBA?

Thanks
Kunal

kunal (BOB member since 2003-03-17)

system · April 4, 2003, 3:44am

There is a VB add-in floating around in here that will let you export the report in PDF, Excel, or TXT format. Check out the code samples area. Sorry I don’t have the link handy.

Cindy Clayton (BOB member since 2002-06-11)

system · April 4, 2003, 4:00pm

Read this topic, for one. That is essentially what was discussed. If you look at the code samples, they are all for saving the report as various output formats, not the data provider cubes.

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · April 4, 2003, 5:13pm

Hi Dave

I went thru the posts again. As I said earlier, my requirement is to
retain the position of tables, cells etc in the report when I
export is to Text

All the examples above (wherever they pertain to exporting in
text format) result in Data cube being exported…not the report.

Adhoc Solution:
I tried doing this in VBA by

saving the report as HTML
Open it in MS Excel
Export it to text

And it worked

Kunal

kunal (BOB member since 2003-03-17)

system · April 4, 2003, 5:16pm

Umm, I think that is a hard way of doing this. In VBA, the option “ExportAsText” saves your report tab, not the data cube. Been doing that on some reports for a while now.

ActiveDocument.Reports.Item("Summary Output").ExportAsText "c:\summaryMonth.txt"

Scott Bowers (BOB member since 2002-09-30)

system · April 4, 2003, 5:23pm

Scott…my aim is to export from report…not data provider :?

kunal (BOB member since 2003-03-17)

system · April 4, 2003, 5:29pm

Scott…sorry for not trying out yr code first…

Your code is aligning my table perfectly when exporting
to text file. Only when I have two tables side by side,
formatting goes for a toss…

Thanks again

kunal (BOB member since 2003-03-17)

system · April 4, 2003, 6:34pm

Ya, even though it says export, that is the same as save as Text.

So basicly, for testing of your format you can manually save as text and see what the output will look like. Not sure on the side by side, never done that before.

Scott Bowers (BOB member since 2002-09-30)

system · April 4, 2003, 7:14pm

Text does not support formatting. You’ll get the data from the report, but the positioning will be limited, at best.

Read them again. When you see a call to ConvertTo, it is being applied to the cube (data provider). When you see a call to ExportAsText or ExportAsPDF that is being applied to the report (or document, if available).

I understand; it can be confusing. The menu item to save the data provider (cube) is Export, while the menu item to save a report as text is Save As. But the VBA equivalents are:

Export => ConvertTo()
Save As => ExportAs{Type}
… where {Type} can be Text, RTF, PDF, or HTML.

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · April 5, 2003, 12:36am

The bottom line is that ‘Export’ or ‘Save As’
{where the target is a text file} does not result in correct
formatting of the report.

I have been able to retain the format of the report by
the earlier steps I recommended.

Using VBA, Export the report into HTML format. Open HTML
file from MS Excel and save from Excel to text. I will post
the code shortly.

Thanks

kunal (BOB member since 2003-03-17)

system · April 5, 2003, 4:50pm

Hi

I am trying out for exporting to Excel with Different Sheet. Ie

I have to get a excel file name
If it is new, then create excel file and export te data to the given Sheet name.
If it is already exist, then create one more sheet(taken as input) and export the data into that sheet and save it.

I have a posting(exactly the previous posting of this one.) and i am getting error subscript out of range when

given like Sheet(inputssheetfilename). move _before Line.

Can anybody give solution.

meetrln (BOB member since 2002-12-03)

system · April 5, 2003, 10:05pm

You may want to take a look at this thread.

Cindy Clayton (BOB member since 2002-06-11)

system · April 6, 2003, 3:15am

Hi cindy

seems very interesting add-in. unfortunately, when i tried to reach out Avaski’s donwload page, its stopped me saying unauthorized entry kind of IE message.

But i am desparately looking forward a kind of code. hope it wold contains creating New worksheet in already existing excel file.

Can you send this add-in to me at rlnsimha2000@yahoo.com

sorry for the inconvenience.

meetrln (BOB member since 2002-12-03)