Suggestion/Help Please

[tmdrake]

I have a form that outputs the results to an Excel workbook. I would like to
upon opening of the excel workbook to create a Macro that uses the
information and creates a Pivot Table then formats the Pivot table.

I am not sure how to do this. I have tried creating a Marco in the workbook
the information is dumped into. But for some reason the Marco doesn't save.

Please help.

 

[ryguy7272]

Sounds like your proprietary software generates a new Excel file each time it
runs. If it is constantly spitting out new Excel files, you will never be
able to build a macro in those files! Create a macro in a 'summary' Excel
file, that opens the newly generated Excel file, and THEN build the Pivot
Table.

This should get you started:
Sub Import()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim x As Long
Dim FilesToOpen


FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Excel Files to Open")

Set Wb1 = ActiveWorkbook
For x = LBound(FilesToOpen) To UBound(FilesToOpen)
Set Wb2 = Workbooks.Open(Filename:=FilesToOpen(x))

Wb2.Worksheets.Copy _
After:=Wb1.Sheets(Wb1.Sheets.Count)

Wb2.Close False
Next x

Sheets("Control Sheet").Select
End Sub
'This will open a window and allow you to import your newly created Excel
file.

Then turn on the macro recorder and build your Pivot Table. Turn the
recorder off when you are done. That's probably 95%-99% of it. The rest
depends on your personal preferences of formatting and the like. Post back
if you have more questions.

Regards,
Ryan---

 

[tmdrake]

Thanks so much for your help, unfortunately, this is way over my head. You
are correct in everytime a query is run from Access, it creates a new Excel
file. Now you lost me after that. The code that you gave, where do I put
this, in Access or Excel. Secondly, please give me step by step insturctions
on creating a Summary worksheet that create another worksheet.

Again, sorry this is so over my head, and your help is truly appreciated.

 

[ryguy7272]

Look here:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm#Creating_Your_First_Macro

That's how you open the Project window. Then, copy and paste the macro that
I gave you into Module1. Click inside the macro (anywhere in the code) and
click the small green triangle near the top of the window. This will run the
code. Then, follow the macro recorder example, below the first example. If
you are new to this, it may be a little frustrating to learn what to do, but
it's worth the effort and you'll get out of it what you put into it.

Regards,
Ryan---