BO data extract and carriage returns

[asylum]

Hi,

I have some code that I use to open a BO report, refresh it and copy the results to a txt file which I then open in excel, import and manipulate data in.

The problem is the BO data contains some carriage returns, so when the code extracts the BO data from the report it puts it in different rows, so that all my data is then messed up when in excel. ie:

in bo cell contains: The quick Brown fox (CR) jumps over (CR) the lazy dog,
so in the text file what i get is:

The quick brown fox
jumps over
the lazy dog

all on seperate lines, which is then how it copys into excel.

the code i am using is:

Dim strPath As String
Dim BOApp As busobj.Application
Dim Doc As busobj.Document
Dim DataProv As busobj.DataProvider
 
strPath = ThisWorkbook.Path
 
Application.DisplayAlerts = False
 
Set BOApp = New busobj.Application
BOApp.Visible = False
 
Call BOApp.LoginAs
 
Set Doc = BOApp.Documents.Open(strPath & "\IOCritreg.rep")
Doc.Refresh
Doc.Reports(1).ExportAsText (strPath & "\Data.txt")
Doc.Save
 
Application.ScreenUpdating = False
 
Doc.Close
BOApp.Quit
Set BOApp = Nothing
 
Workbooks.OpenText Filename:=(strPath & "\Data.txt"), Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 4))
 
Range("A3").Select
Range("A3:H5002").Select
Selection.Copy
 
Windows("Critical Registers weekly report.xls").Activate
 
Sheets("DATA").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
 
Windows("Data.txt").Activate
ActiveWindow.Close
 
Windows("Critical Registers weekly report.xls").Activate
Sheets("Data").Select
 
ActiveWorkbook.Save
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
End Sub

Is there something i can put in the BO extract to .txt file part of teh code to stop this, it really is fouling things up, and no, I can't go and strip the base data that BO is pointing at!

Thanks,and Help!

Andy

 

[asylum]

Hi All,

Thanks for your help and pointers, Yes, BO can port out t excel, unfortunatley the same problems occurs. I have now been able to sove it and it was at SQL level within the business objects query. So just in case someone else has the same problem here is the solution:

replace(replace(***DATA ITEM***, chr(13), ''),chr(10), ' ')

this line needs to be in your SQL query in BO where the DATA ITEM replaces the line returning the data which is causing you problems. It replaces all the line feeds (ASCII character 10) and Carriage returns (ASCII character 13) in the data before the results are displayed in BO. note that in SQL the command CHR is used as opposed to CHAR! (had me foxed for a while).

Many thanks for all your help,

Andy