New Worksheet (A2K)

Topic

New Reply

Good Morning,

If I’m posting in the incorrect lounge, I apologize.

I have data w/in ACCESS that I export to an EXISTING Workbook. This workbook should be updated on a bi-weekly basis. However, each time I run the report, I’m copying and pasting the data into a new worksheet within the existing workbook and then naming the tab for the current week the report was ran. Ideally, each week, I would like to run my report using automation (vba).

1. Export ACCESS data to a NEW WORKSHEET within the existing WORKBOOK
2. Name the new worksheet accordingly (by the week processed)
3. Maintain the formating of a specific worksheet

Is this possible? If so, is it complicated? If so, can someone point me to EXCEL samples I can follow.

I’m somewhat knowledgeable with vba, although, primarily in ACCESS, but I believe I can follow samples, if needed.

Reply | Quote

Replies

There is an example of automating Excel from Access in post 654,900. If you search the Access forum for Excel.Application, you’ll find more.

Reply | Quote

Thanks Hans for the response. I took your advice and found bits and peices and was able to put something together. The process works, however, I’m still having a few minor problems. I’m certain it’s code placement, but can’t figure it out.

The code to export (paste) the data from Access to Excel resides within Access except for the portion for the margins. Access doesn’t recognize the portion outlined in the attachment, so I’ve placed it within Excel as a macro and call the macro within Access.

I’ve attached my code.
The issues I’m having a problem with:

• I ONLY want A1:K2 to be BOLD but if I bold one area, it all bolds, if I don’t, none of it’s bold
• Autofit Column
• Autofit Row[/list]Drawbacks:
  • The process can be viewed while it’s running
  • I must close Acces first to view the spreadsheet[/list]If you have any suggestions, I would certainly appreciate it.

Reply | Quote

When using Automation, you must take great care to refer ALL Excel objects directly or indirectly to the Excel application object. You should NEVER use something like

Sheets(“Main”).Select

because that way, Sheets does not belong to objXL or objWkb. Use

objWkb.Sheets(“Main”).Select

instead. On a further note, it’s generally not necessary to select sheets and cells in a macro.

See attached version.

Reply | Quote

Thanks Hans….wouldn’t have been able to solve it on my own, EXCEL vba I know even less about than Access vba.

I did however make 1 minor change.

Changed:
objSht.Range(“A1”).PasteSpecial Paste:=xlformats

To
objSht.Range(“A1”).PasteSpecial Paste:=xlAll

and all works as expected.

As always, thanks

Reply | Quote