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

 

[Richard Schollar]

I presume you also have Carriage Return-Line Feed combinations in this file to delimit the different report lines? Shouldn't cause a problem as Excel should recognise just LineFeeds fine. Without going into your code in detail, you could plonk the following routine just before you do the Workbooks.OpenText line which will strip all CRs from the file:

Dim i As Freefile
Dim strBuffer As String

i = FreeFile
strBuffer = Space(FileLen(strPath & "\Data.txt"))

Open strPath & "\Data.txt" For Binary Access Read As #i
  Get #i,,strBuffer
Close #i
strBuffer = Replace(strBuffer,Chr$(13),vbNullString)
Kill strPath & "\Data.txt"
Open strPath & "\Data.txt" For Binary Access Write As #i
  Put #i,,strBuffer
Close #i

 

[asylum]

Hi Richard,

thanks for that but the problem actually comes bfore that stage, it when the data is copied from the BO app, and into the txt file. This actually also happens if I manually copy and paste, so i need a sloution 'on the fly' i think so that the data does not get pasted into the txt file with CR's in.

Andy

 

[Richard Schollar]

Well, I know nothing about BO or the object model it exposes, so you are correct that it does not remove the CRs before the export. It should still solve the problem though - did you try it?

 

[asylum]

thanks, have tried it, but VB doesn't know what freefile is?? is it in a later version of VB, have office 2003 here,

Andy

 

[Norie]

Try Dim I As Integer.

 

[asylum]

aha! - dim i as integer!

I shall try again!

 

[asylum]

well it runs, but doesn't make any difference...

 

[Richard Schollar]

How do you know it is Carriage Returns giving you the problem? Have you checked what their ascii value is eg using the Asc function in VBA? This is important as what I suggested obviously won't work if it is LineFeeds rather than CRs causing the problems.

 

[Norie]

Andy

What version of Business Objects are you using?

Don't versions after 5.1.4 have an option to export queries/reports directly to Excel?