2

I'm very new with VBA Excel and i only know the things as far as i need for this report formatting task. My code is almost completed, but what i wonder is, how can i make my program work on several documents?

I mean, i want to choose an excel file via my program, then i want to start the process of report formatting. Then maybe user need to format another document, i want my program to be able to format that document too. how can i achieve that?

Thanks in Advance

Timur

Community
  • 1
  • 1
t1w
  • 1,408
  • 5
  • 25
  • 55
  • Your `this document` will work as the application with a `user form` to control/format other documents. – bonCodigo Nov 30 '12 at 09:00
  • So, in order to use this program to manipulate other files, my current file must be saved because i cannot seperate them? How can i add a file chooser control to my form and use my code to manipulate my target file? – t1w Nov 30 '12 at 09:13
  • You are in the right direction. You can even have a report template within your master control file. [check this link](http://stackoverflow.com/questions/221444/browse-for-a-file-from-excel-vba) – bonCodigo Nov 30 '12 at 13:04
  • The documents that need to be formated are always the same files or they can also be newly created files? I ask this because maybe you can include a format macro on those documents and call it from your "main" file. See this [link](http://stackoverflow.com/questions/2806065/running-excel-macro-from-another-workbook) – Octavio Dec 02 '12 at 00:56
  • Hi @Octavio, Thanks for reply. Actually the documents that need to formatted are our analysis results from CAST AI Platform and we need to format it for IBM RTC. Our client is a bank and my program is suppose to work on a several projects' report formatting task. I think in my case eventhough data are different, my initial and target tables' column headers are same for every project – t1w Dec 03 '12 at 07:09

1 Answers1

1

The way I am currently doing this is by creating an Excel add-in. To do this, place your macros and forms into a new, empty Excel workbook and save as a .xlam document.

To use the macros, open the your Excel file and the add-in. To open the add-in automatically, save it to "C:\Program Files\Microsoft Office\Office12\XLSTART" (Vista).

You can even create a custom ribbon for your add-in using the Custom UI Editor for Microsoft Office http://msdn.microsoft.com/en-us/library/office/ee691832(v=office.14).aspx (The download link is at the end of the instructions)

mjoshawa
  • 115
  • 8
  • Hi, Thanks for reply. I think this is the solution that i'm looking for. But can you explain whay you mean by ** " To use the macros, open the your Excel file and the add-in " ** ? :) I'm little bir confused because i'm new with vba. I understand tha part opening excel file but how can i open the add-in? By add-in do you mean vba editor? :S Shall i press ALT+F11 ? :) :) (a) – t1w Dec 04 '12 at 14:11
  • Sorry for the confusion. When you save a workbook with the .xlam file extension it becomes an Excel add-in. Add-ins can be open in the same Excel instance as any workbook. This allows you to access all of the macros in the add-in from whatever workbook you are working on. For example, imagine you created an add-in called ExAddin.xlam and a workbook Work.xlsx. You would open Work.xlsx then ExAddin.xlam. Then the macros in ExAddin.xlam would be accessible in Work.xlsx. – mjoshawa Dec 05 '12 at 19:22
  • So, in order to make it working on other excel file, i should run my initial file first? :s (pardon my confusion, i'm not native in english) – t1w Dec 07 '12 at 08:53
  • Simply open whatever file you want to work on, then open the Excel add-in (your .xlam file). Sorry, I don't know how else to explain it. This will be much more clear after you make your first add-in. – mjoshawa Dec 07 '12 at 18:03