Is there a way to "derive" the format of an excel file in VBA?

[Access9001]

I have a file that is rather specifically formatted in terms of colors/cell sizes/etc that I would like to be able to recreate and edit dynamically.

Is there a way I can basically say "recreate this entire structure" in terms of code? Is there a way I can go into an excel sheet and basically have it derive VBA that would recreate the same output?

 

[gemma-the-husky]

yes - you can open a work sheet, and address and manage cells programmatically

one way is maybe to have a predesigned template - open thatr, populate it, and save it as something else

that way, you dont have to reformat the template each time

 

[Access9001]

Right, but I mean I have a page someone already made by hand, and it's formatted and structured in a very specific way (custom-width cells, colors, etc).

Is there a way I can, with VBA, determine the VBA-equivalent of recreating the sheet?

 

[gemma-the-husky]

thats what i mean

design it in excel, with the colors/fonts etc - and then just populate the values with vba

you can do anything of course, but its reinventing the wheel a bit, doing all the formatting in vba - as well as being cumbersome working out all the vba commands to interact with a spreadsheet.

 

[Access9001]

It's an excel sheet I want to be able to recreate through an Access module, which is why I was curious. Some of the formatting would be *slightly* different depending on the values passed, but for the most part, the entire sheet is something I'd like to just have the code equivalent for so I could modify what I needed at will.

Is there a way to do this in VBA?

 

[SOS]

Is there a way to do this in VBA?

Okay, you keep asking that question and the answer keeps coming back to you but you seem to ignore it.

The answer is YES, you CAN do that in VBA.

If you want to see a little taste of what you can do, check out this code here which has some formatting at the end of the module. Basically, anything you can do manually, you can do via code. And if you want to know how, just record a macro in Excel, doing what you want to do and then look at the resulting code. You can modify that slightly and fit it into Access VBA and it works great.

 

[Access9001]

Sorry, I must not be articulating myself properly.

I am fully aware that the backend for Excel is VBA and that everything CAN be done in code. I also understand that you can Record Macro, format things, and have the VBA output that way.

What I do not know, however, is whether or not I can simply open an Excel sheet and have it "derive" the underlying VBA that *would* recreate the page *without* needing me to record a macro/reformat everything/etc.

 

[SOS]

No, you would have to code it.

 

[Brianwarnock]

I am sure that you cannot "reverse engineer" the sheet, however you don't need to, as Dave (aka Gemma) has said save the current design as a template and use that using VBA to modify and populate, afterall that is what you do anyway, what you need to know is how to open which template. Hmm?

Brian

Looks ok from Help