Hi

I need a tool, that can analyse existing Excel Sheet, and generate
Visual Basic 6.0 / VBA code, able to generate this sheet from scratch
(add new blank sheet, apply all changes).

I'm interested in column/row sizes, cell colors, borders, cell values,
and formulas. I don't need support for VBA.

Does anybody known such tool?

Hi Tomasz:

First create a template worksheet with all you formatting in place. Then
its just a matter of pulling-down:

Edit Move or Copy Sheet... and select template and click make a copy.

If you are comfortable with VBA:

Sub Macro1()
Sheets("template").Copy After:=Sheets("template")
End Sub
--
Gary's Student


"Tomasz Klim" wrote:

Hi

I need a tool, that can analyse existing Excel Sheet, and generate
Visual Basic 6.0 / VBA code, able to generate this sheet from scratch
(add new blank sheet, apply all changes).

I'm interested in column/row sizes, cell colors, borders, cell values,
and formulas. I don't need support for VBA.

Does anybody known such tool?

Yes, I know that way. But I have a very complicated sheet. Well, the sheet
itself is very simple, but has many many columns, rows etc., and VBA code
inserting data from database into these rows/columns.

So each time I want to add new row between existing rows, I have to change a
few hundreds of row references. And if I got code generating this sheet from
scratch, I could modify it and put in some rules, that would simplify my
job.

I'm asking, because I've seen such a sheet-parser, that generates a set of
forms in PHP - so maybe there is one for Visual Basic...


U¿ytkownik "Gary''s Student"
napisa³ w wiadomo¶ci
...
Hi Tomasz:

First create a template worksheet with all you formatting in place. Then
its just a matter of pulling-down:

Edit Move or Copy Sheet... and select template and click make a copy.

If you are comfortable with VBA:

Sub Macro1()
Sheets("template").Copy After:=Sheets("template")
End Sub
--
Gary's Student


"Tomasz Klim" wrote:

Hi

I need a tool, that can analyse existing Excel Sheet, and generate
Visual Basic 6.0 / VBA code, able to generate this sheet from scratch
(add new blank sheet, apply all changes).

I'm interested in column/row sizes, cell colors, borders, cell values,
and formulas. I don't need support for VBA.

Does anybody known such tool?

If you do this process manually, turn on the Macro Recorder. At a minimum
you would get a macro that you could then edit for changes.

Inserting rows should not cause you to edit formulae.


Look for the over-use of absolute references and remove them. Things like
=$A$3 can cause problems.
--
Gary's Student


"Tomasz Klim" wrote:

Yes, I know that way. But I have a very complicated sheet. Well, the sheet
itself is very simple, but has many many columns, rows etc., and VBA code
inserting data from database into these rows/columns.

So each time I want to add new row between existing rows, I have to change a
few hundreds of row references. And if I got code generating this sheet from
scratch, I could modify it and put in some rules, that would simplify my
job.

I'm asking, because I've seen such a sheet-parser, that generates a set of
forms in PHP - so maybe there is one for Visual Basic...


U¿ytkownik "Gary''s Student"
napisa³ w wiadomo¶ci
...
Hi Tomasz:

First create a template worksheet with all you formatting in place. Then
its just a matter of pulling-down:

Edit Move or Copy Sheet... and select template and click make a copy.

If you are comfortable with VBA:

Sub Macro1()
Sheets("template").Copy After:=Sheets("template")
End Sub
--
Gary's Student


"Tomasz Klim" wrote:

Hi

I need a tool, that can analyse existing Excel Sheet, and generate
Visual Basic 6.0 / VBA code, able to generate this sheet from scratch
(add new blank sheet, apply all changes).

I'm interested in column/row sizes, cell colors, borders, cell values,
and formulas. I don't need support for VBA.

Does anybody known such tool?

I also know this. But this sheet is already done, so recording wouldn't
help.

The main problem is that I have VBA code with SQL queries, that use CASE
statements to generate row/column numbers:
Cells(RS("row"),RS("column"))=RS("sum")
where the column number is stored in database, and row number is computed
from:
case when x='green' then 3 when x='red' then 4 else 2 end as row

So inserting a new row requires me to edit all these SQL queries.


U¿ytkownik "Gary''s Student"
napisa³ w wiadomo¶ci
...
If you do this process manually, turn on the Macro Recorder. At a minimum
you would get a macro that you could then edit for changes.

Inserting rows should not cause you to edit formulae.


Look for the over-use of absolute references and remove them. Things like
=$A$3 can cause problems.
--
Gary's Student


"Tomasz Klim" wrote:

Yes, I know that way. But I have a very complicated sheet. Well, the
sheet
itself is very simple, but has many many columns, rows etc., and VBA code
inserting data from database into these rows/columns.

So each time I want to add new row between existing rows, I have to
change a
few hundreds of row references. And if I got code generating this sheet
from
scratch, I could modify it and put in some rules, that would simplify my
job.

I'm asking, because I've seen such a sheet-parser, that generates a set
of
forms in PHP - so maybe there is one for Visual Basic...


U?ytkownik "Gary''s Student"
napisa3 w wiadomo?ci
...
Hi Tomasz:

First create a template worksheet with all you formatting in place.
Then
its just a matter of pulling-down:

Edit Move or Copy Sheet... and select template and click make a
copy.

If you are comfortable with VBA:

Sub Macro1()
Sheets("template").Copy After:=Sheets("template")
End Sub
--
Gary's Student


"Tomasz Klim" wrote:

Hi

I need a tool, that can analyse existing Excel Sheet, and generate
Visual Basic 6.0 / VBA code, able to generate this sheet from scratch
(add new blank sheet, apply all changes).

I'm interested in column/row sizes, cell colors, borders, cell values,
and formulas. I don't need support for VBA.

Does anybody known such tool?