URGENT - Unable to save with 'TEXT' format using OLE

[Former Member]

I have this piece of code where I am trying to save multiple datawindows to a single Excel sheet. This code works, but, has a problem. A column has a 14 digit number (which is actually a character field), which has leading zeroes. When the file is getting saved (The line lole_MainWorkBook.SaveAs(ls_path) ), the leading zeroes are getting stripped off

I tried using lole_MainWorkBook.ActiveSheet.Columns("C").NumberFormat = "@". It works, but, the line lole_sheet.Paste gives me an error 'Unable to find object function Paste'. If I comment out the line lole_MainWorkBook.ActiveSheet.Columns("C").NumberFormat = "@", the Save works just fine

Any help is greatly appreciated

//Save the Error and Success reports

ls_filename = GetCurrentDirectory() + '\' + ls_sheet_list[ll_ctr] +'.txt'

ls_sheetname = ls_sheet_list[ll_ctr] ldw_dw_list[ll_ctr].SaveAsFormattedText(ls_filename, EncodingANSI!, "~t", "", "~r~n", TRUE)

//Copy content from Saved File for UCCNet

lole_BufferWorkBook = lole_Excel.WorkBooks.Open(ls_filename) lole_BufferWorkBook.Worksheets(1).Cells.Select

lole_Excel.CutCopyMode = False

lole_BufferWorkBook.Worksheets(1).Cells.Copy

//Paste to current sheet lole_MainWorkBook.Activate

lole_sheet = lole_MainWorkBook.WorkSheets(ll_ctr)

lole_sheet.Activate

lole_sheet.Name = ls_sheetName

If ls_sheetName = 'UCCNet_Errors' then lole_MainWorkBook.ActiveSheet.Columns("C").NumberFormat = "@"

End If

lole_sheet.Paste

lole_sheet.Cells.EntireColumn.AutoFit

lole_Excel.CutCopyMode = False

lole_BufferWorkBook.Close(False)

[chris_keating]

I believe that you have to use the method

Workbooks.OpenText(...)

correctly setting the FieldInfo array to set the XIColumnDataType to Text.

[chris_keating]

I believe that you have to use the method

Workbooks.OpenText(...)

correctly setting the FieldInfo array to set the XIColumnDataType to Text for the column.

[Former Member]

Hello Patricia - Thanks for your response. I did try this, but looks like it's not working since the paste operation still get rids of the zeroes

I added a NumberFormat of 14 zeroes (or how many ever digits long the number has to be) and it worked

I added an "@" to the first digit and then replaced it with blanks after setting the NumberFormat to 14 zeroes. Modified code here:

//Save the Error and Success reports ls_filename = GetCurrentDirectory() + '\' + ls_sheet_list[ll_ctr] +'.txt' ls_sheetname = ls_sheet_list[ll_ctr] ldw_dw_list[ll_ctr].SaveAsFormattedText(ls_filename, EncodingANSI!, "~t", "", "~r~n", TRUE) //Copy content from Saved File for UCCNet lole_BufferWorkBook = lole_Excel.WorkBooks.Open(ls_filename) lole_BufferWorkBook.Worksheets(1).Cells.Select lole_Excel.CutCopyMode = False lole_BufferWorkBook.Worksheets(1).Cells.Copy //Paste to current sheet lole_MainWorkBook.Activate lole_sheet = lole_MainWorkBook.WorkSheets(ll_ctr) lole_sheet.Name = ls_sheetName lole_sheet.Paste lole_sheet.Cells.EntireColumn.AutoFit lole_sheet.Columns("C").NumberFormat = "00000000000000" lole_sheet.Columns("C").Replace("@", "") lole_Excel.CutCopyMode = False lole_BufferWorkBook.Close(False)

[former_member1087012]

Hi

How did you solve the problem, do you have the code?

Regards

[former_member202249]

Hi Krisha,

This has been discussed previously and is how Excel handles leading zeros even when declared as text.

See below links for a suggestion to pre-pend an apostrophe to the numeric value to retain the leading zeroes.

https://archive.sap.com/discussions/thread/1392955

http://codeverge.com/sybase.powerbuilder.datawindow/lose-leading-zero-when-saving/824369

Pat

[Former Member]

There is another problem that I see that happens only on few of the user machines

lole_Excel.ConnectToNewObject("excel.application")

lole_BufferWorkBook = lole_Excel.WorkBooks.Open(ls_filename)

The Open function throws an error 'Error calling external object function open'. Almost all of them have Excel 2016 on their computers

Any ideas/suggestions?