VBA and Excel

[kriemer]

I have a number of spreadsheets that I would like to: open Spreadsheet #1, run a macro, close, then open Spreadsheet #2, etc.

None of the code samples I try work. Typically, the spreadsheet opens, but in some cases the macro does not execute, in other the macro fails.

Is there a recommended approach for this functionality?

Regard and thanks in advance.

k

 

[ajetrumpet]

Yes there is. But it depends on if you have the Office Version of Excel's object library references in your VBA reference list, as I've unfortunately figured out! If you do, the code would be something like this:

Your Function Name()

dim objexcel As new excel.application, i As integer
dim docexcel As excel.workbook

  for i = 1 to 4
    set docexcel = objexcel.workbooks.open("FILE PATH HERE")
      objexcel.visible = true
        REFERENCE YOUR MACRO CODE HERE so it can run itself.
          docexcel.close
    set docexcel = Nothing
  next i

set objexcel = Nothing
set docexcel = Nothing

End function

Of course, I have made it easy as to reference files that end with sequential numbers. If you want to create a loop like this, you'll probably have to have supportive file names that can compliment loops with integers or other variables that are sequential in nature.


Note: If you do NOT have the object library loaded, you have to use

dim objexcel as object

and create the application with:

set objexcel = createobject("excel.application")

 

[kriemer]

Many thanks for your help and speedy reply.

Now to re-ask the question as I originally meant to.

Is there any way to accomplish this using VBS? No problem hard coding the names into the routine (though you method is certainly slicker).

Thanks for your help.

k

 

[ajetrumpet]

Now, to REITERATE what I already said, "NO, there is not an easy way, unless you loop it with a variable like i did".

If you have to hard code the names in, that's what you have to do, it's that simple. Or, if you have the names or paths of the files in a form somewhere, or anywhere else that you can REFERENCE in the code instead of just typing them in, you can create loops easy that way too...

I'm not going to write the code for you. I figure that I've given you a great place to start from. Good luck!

 

[kriemer]

ajetrumpet

Now I feel ignorant and rude; I didn't mean to suggest you write my code.

What I thought I was asking about was a similar approach using VBS which can run in the host environment.

Obviously I was not clear and I apologize.

a

 

[ajetrumpet]

Perhaps I was instead of you. I think I may have mixed the signals. I apologize too. I don't think I can help with that, but maybe oen of the other real experts here can. Sorry for the confusion, and good luck with the project...

 

[georgedwilkinson]

You should take a look at the Autpen() subroutine in Excel, for one. It kinda depends on whether your spreadsheets do more than one thing.

If you need much more, you're going to need to post what you've got so far with a description of what works right and what doesn't work right.

 

[kriemer]

I do a lot of rejiggering of the spreadsheets so I have been trying to avoid using the "Autpen()" sub name feature.

I have found the code below which will open the spreadsheet but doesn't seem to run the macro. In some cases the spreadsheet will close but the macro will not have run at all, in other cases the macro crashes.

Dim objXL
Set objXL = CreateObject("Excel.Application")
on error resume next
With objXL 
    .Workbooks.Open ("c:\temp\test2.xls") 
    .Run "test2.xls!test" 
    .Quit
End With
Set objXL = Nothing

Any suggestions will be welcome.

Thanks

k

 

[chergh]

If you change the line:

.Run "test2.xls!test"

to:

.Run "test"

It should work.

Make sure your macros are in a module as well. If you have the code in the workbook or a worksheet then the code will become a method of those objects so can be called like:

'For the workbook
set objWB = objXL.Workbooks.Open ("c:\temp\test2.xls") 
objWB.test

'for worksheet
set objWB = objXL.Workbooks.Open ("c:\temp\test2.xls") 
set objWS = objWB.worksheets("SheetNAme")
objWS.test

 

[kriemer]

chergh, et al.;

And as always, the magic is in the details.

Many thanks for the solution and sorry for taking so long to reply.

k