autofit for more than once sheet in excel & single line with comms seperated and gene

[saratht]

I am working on the issue with is having more records my excel sheet 9 sheets and i want to autofit them where i am doing it i am geting errors .

lchWorkSheet:range("A1:AI1000"):SELECT.
lchExcel:Selection:Columns:AutoFit.
lchWorkSheet:range("A1"):SELECT.

pleas post the code for this and also where cell values are created with indiviual manner where the vchWorkSheet:Range("AD" + STRING(vrow)):VALUE it is taking lot of time.

is their any way we can take single line with comms seperated and generate mutliple cells ..... in saveas conditioning.(not open)..

 

[Osborne]

Re: autofit for more than once sheet in excel & single line with comms seperated and

I think AutoFit has to be used with a Range and not selection but am not completely sure on that. What happens if you try:

lchWorkSheet:range("A1:AI1000"):AutoFit.
OR
lchWorkSheet:range("A1:AI1000"):Columns:AutoFit.
OR
lchWorkSheet:Columns("A1:AI1000"):AutoFit().

Populating each cell can be slow and don't know of a quicker way apart from creating a CSV file and then using COM HANDLES to open/import in Excel.

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

but i need to created 9 sheets in one excel

 

[Osborne]

Re: autofit for more than once sheet in excel & single line with comms seperated and

To create 9 worksheets in Excel do you mean something like this:

chExcelAppl:SheetsInNewWorkbook = 9.
/* Then to access and populate */
DO i = 1 TO 9:
   chWorksheet = chExcelAppl:Sheets:Item(i).
   chWorksheet:Name = "Data Sheet" + STRING(i).
   /* Write data */
END.

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

Any method to excel file creation with delimitor. so i can speed up the excel sheet creation .

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

When vchWorkSheet = vExcel:Sheets:ITEM(l-count). i am assign . in loop is it increase the time or decrease. Also i am assign the cell and using Autofit then it is giving error.

 

[enoon]

Re: autofit for more than once sheet in excel & single line with comms seperated and

Well I had some speed issues too. But what I did in one of the cases is turning off the auto refresh in Excel (you have to digg for this one, cause I'we lost the source code, there is a VBA command to turn this feature ON/OFF). On the other case I filled an excel on the disk, deleted the handle and the opened it as an existing excel which runs quite fast, even for big excel files.

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

can please tell in which sites these excel related procedures present

 

[enoon]

Re: autofit for more than once sheet in excel & single line with comms seperated and

can please tell in which sites these excel related procedures present

Ok, I did the google for you so here it is:

[SIZE=+1]Tip #4 : Turn off screen updating, recalculation[/SIZE]
If you're working with multiple sheets, screen refresh can slow you down and distract your user. Put Application.ScreenUpdating = False at the top of your routine and it will freeze screen updating until all your code has stopped executing.
Similarly, you can suspend auto-recalculation with Application.Calculation = xlManual, and return it to auto at the end with Application.Calculation = xlAutomatic


Source: http://www.avdf.com/\apr98\art_ot003.html

 

[Osborne]

Re: autofit for more than once sheet in excel & single line with comms seperated and

Any method to excel file creation with delimitor. so i can speed up the excel sheet creation .

There are a couple of Progress solutions that look useful which you could adapt:

P8426 - How to export Progress data to a file (.csv) readable with Ms Excel.
p98746 - How to access the EXCEL instance created in a Procedure after the close of that Procedure?

Hope this helps.

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

thanks i going implement my taking text file.

 

[sphipp]

Re: autofit for more than once sheet in excel & single line with comms seperated and

Any method to excel file creation with delimitor. so i can speed up the excel sheet creation .

The easiest way is to write out the data to a scratch file, open the scratch file in Excel, copy the new sheet to your existing workbook, rename the newly created sheet and close the scratch file. That way you can create multiple sheets very quickly.

Any extra formatting/formulae can be done on each individual sheet afterwards and should be fairly quick, unless you want fancy/complex formatting/formulae.

The following code should give you an idea of how to do the open/copy/rename/close - you would create the text file according to the data that you need in the spreadsheet.

DEFINE VARIABLE cTempFile AS CHARACTER  NO-UNDO.
DEFINE VARIABLE excelAppl AS COM-HANDLE.
ASSIGN ctempfile = "c:\test1.csv".
CREATE "Excel.Application" excelAppl. 
excelAppl:Workbooks:Open ("h:\test\test1.xls").
excelAppl:visible = yes.
RUN ipAddSheet (1).
RUN ipAddSheet (2).
RUN ipAddSheet (3).
RUN ipAddSheet (4).
RUN ipAddSheet (5).
RUN ipAddSheet (6).
RUN ipAddSheet (7).
RUN ipAddSheet (8).
RUN ipAddSheet (9).
 
excelAppl:Workbooks:Item(1):Save().
excelappl:workbooks:item(1):Close().
excelAppl:Quit().
release object excelAppl no-error.
PROCEDURE ipTempSheet:
    DEFINE INPUT  PARAMETER picount AS INTEGER    NO-UNDO.
    DEFINE VARIABLE iloop AS INTEGER    NO-UNDO.
    DEFINE VARIABLE imult AS INTEGER    NO-UNDO.
 
 
 
    OUTPUT TO VALUE (ctempfile).
    PUT UNFORMATTED "New Sheet " picount SKIP.
    DO iloop = 1 TO 20:
        PUT UNFORMATTED iloop "X,".
    END.
    PUT UNFORMATTED SKIP.
    DO iloop = 1 TO 20:
        DO imult = 1 TO 20:
            PUT UNFORMATTED iloop * imult ",".
        END.
        PUT UNFORMATTED SKIP.
    END.
    OUTPUT CLOSE.
END PROCEDURE.
PROCEDURE ipAddSheet:
    DEFINE INPUT  PARAMETER piSheet AS INTEGER    NO-UNDO.
    RUN ipTempSheet (piSheet).
    excelAppl:Workbooks:Open (ctempfile).
    excelAppl:Workbooks:ITEM(2):sheets(1):copy(, excelAppl:Workbooks:ITEM(1):sheets(piSheet)).
    excelAppl:Workbooks:ITEM(1):sheets(piSheet + 1):name = "NEW Sheet " + STRING (pisheet).
    excelappl:workbooks:item(2):Close().
END PROCEDURE.

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

greate Thanks for sending this example..

 

[sphipp]

Re: autofit for more than once sheet in excel & single line with comms seperated and

I am working on the issue with is having more records my excel sheet 9 sheets and i want to autofit them where i am doing it i am geting errors .

lchWorkSheet:range("A1:AI1000"):SELECT.
lchExcel:Selection:Columns:AutoFit.
lchWorkSheet:range("A1"):SELECT.

pleas post the code for this and also where cell values are created with indiviual manner where the vchWorkSheet:Range("AD" + STRING(vrow)):VALUE it is taking lot of time.

You can use entirecolumn when selecting the first row of a range and autofit works for those columns.

lchExcel:Workbooks:ITEM(1):sheets(1):Range("a1:z1"):SELECT ().
lchExcel:selection:entirecolumn:AutoFit ().

 

[saratht]

Re: autofit for more than once sheet in excel & single line with comms seperated and

hi once i am saving the excel file using
chWorkBook:SaveAs(iFileDir,43,,,,,).
when i am selecting again that file to override then i am geting this error.

---------------------------
Error (Press HELP to view stack trace)
---------------------------
Error occurred while accessing component property/method: SaveAs.
Cannot access 'finalcut1.xls'.
Error code: 0x80020009 IP_Generate_Excel sys/p_mi_cum.p (5890)
---------------------------
OK Help
---------------------------

 

[sphipp]

Re: autofit for more than once sheet in excel & single line with comms seperated and

Have you run this before successfully and checked it in Excel?

I'm guessing that you still have it open in Excel.

Make sure you close all your instances of Excel, then open 1 instance of Excel, open the file to make sure you can read it (i.e. it isn't locked for editing) , then close it and run the program again.

 

[saratht]

How To change Format of browser only particular coloumn by using widgets.

i need to change the format fo the browser column when large value is stored in it. i have to assign column format. where i defining the browser & columns in my program where initially with "x(27)" ?