This is another VBA tips I learned with my Excel VBA project. Just wanted to share here for the future reference. Hope you can find it useful as well. The original post was found on tek-tips.com.
The following procedures demonstrate creating, copying, moving and deleting of files and folders using VBA: Continue reading
Recently I have been working on a VBA project, so I will post some skills or tips I learned and used on Excel VBA program. Today I will share how to change the cell background color in the VBA module.
The property of background color is , and the color is used as Index format. So to setup a cell (row ID: 1, column ID: 12) in Red background color is the below:
Worksheets(1).Cells(1,12).Interior.ColorIndex = 3 ‘change the background color to RED
And you can find more color index in the rest of my post. Continue reading
As I indicated in my previous post, I needed to rename thousands Microsoft Word documents based on the updated old/new catalog ID pairs.
But that was just one part of my requirement. Since these Word DOC files have the old catalog IDs inside the content which need to be updated with new IDs as well. The methods of my previous post could only resolve the file name part but not the contents.
I started with some existing utilities I found on the Internet but they were not working as I hoped.
1), I tried the Useful File Utility along with its free plug-in “Batch Replacer for MS Word” (Useful File Utility is a shareware which can allow you to use 15 times for free, then $29 for a single user license). It looks promising as it indicated on its web page, but it failed on me with too many un-successful replaces in a few hundreds files.
2), I was impressed with this VBA macro I found on Internet named VBA Find and Replace. But it could not work well with the Find and Replace text list. I always get an error saying my name list is not valid.
After one night’s trying, I decided to write my own script to handle such automate job.