VBA to open, edit, save and close

**AZZ70** · 08-22-2012, 05:23 PM

I Need a VBA to open, edit, save and close a number of excel files in a given folder.
the cells i need to change are F5,I5 and P5. All cells are in the same place on each wookbook sheet, all the wookbooks are in the same folder. I have to update them from MIN to MIN1,MIN2 and MIN3 otherwise the consolidation does not work correctly. i would manually do these changes but its something that will be an on going problem.

SO far i just have a macro to do this but with 500 files at once to change it takes some time. I would prefer it to be automated.

PHP Code:


Sub Macro1() 
' 
' Macro1 Macro 
' 
' Keyboard Shortcut: Ctrl+w 
' 
Range("F5").Select 
ActiveCell.FormulaR1C1 = "MIN1" 
Range("I5").Select 
ActiveCell.FormulaR1C1 = "MIN2" 
Range("P5").Select 
ActiveCell.FormulaR1C1 = "MIN3" 
Range("G5").Select 
ActiveWorkbook.Save 
ActiveWindow.Close 
End Sub

**patel45** · 08-23-2012, 01:55 AM

Please Login or Register  to view this content.

**HaHoBe** · 08-23-2012, 02:10 AM

Hi, pael45,

shouldn´t your code feature Formula or FormulaR1C1 instead of Value?

Could you please explain why you use another instance of Excel instead of the one running with the code?

My complier won´t like mFolder as it hasd not been dimmed. Maybe you should alter your VBE to always show Option Explicit if a new module is inserted (won´t affect those that have been created prior to that).

Ciao,
Holger

**patel45** · 08-23-2012, 04:24 AM

there is no reason, the code may be this

Please Login or Register  to view this content.

shouldn´t your code feature Formula or FormulaR1C1 instead of Value?

I don't understand the question, the user wants paste strings

**HaHoBe** · 08-23-2012, 04:32 AM

Hi, patel45,

maybe you´re right about the pasting but if I read Min I think WorksheetFunction.Min.

You dim aFolder, set mFolder to a value, and then again use aFolder.

Option Explicit maybe a very good hint to follow and avoid typos like these.

Ciao,
Holger

**AZZ70** · 08-23-2012, 11:22 AM

Thanks I will test on Friday and post the results thanks for your help

**AZZ70** · 08-24-2012, 10:43 AM

BRILLIANT, works!

**Cutter** · 08-24-2012, 01:40 PM

@ AZZ70

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**AZZ70** · 08-24-2012, 02:01 PM

I was aware but did not know how to change this. thanks for the help.

VBA to open, edit, save and close

LinkBack

Thread Tools

Rate This Thread

Display

VBA to open, edit, save and close

Re: Vab to open, edit, save and close

Re: Vab to open, edit, save and close

Re: Vab to open, edit, save and close

Re: Vab to open, edit, save and close

Re: Vab to open, edit, save and close

Re: Vab to open, edit, save and close

Re: VBA to open, edit, save and close

Re: VBA to open, edit, save and close

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions