Excel Sheet generator

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" <[email protected]>
napisał w wiadomości
news:[email protected]...
> 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" <[email protected]>
> napisaÂł w wiadomoÂści
> news:[email protected]...
> > 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" <[email protected]>
napisał w wiadomości
news:[email protected]...
> 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" <[email protected]>
>> napisa3 w wiadomo?ci
>> news:[email protected]...
>> > 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?
>> >>
>> >>
>> >>
>>
>>
>>