0 votes
in VBA by Beginner (5 points)
Have spent countless hours researching this issue with absolutely NO success. Just about ready to give up, but I really want to continue my Excel Study. Please HELP!!

1 Answer

+1 vote
by Super Expert (2.7k points)
selected by
 
Best answer

From a vbs file (vbscript), you create an Excel Object by running a code like this:

Set objExcel = CreateObject("Excel.Application")
objExcel.visible=True
Set objWB = objExcel.Workbooks.Add()

The first line creates an instance of Excel but it won't become visible until you make it visible and open a workbook using the 2nd and 3rd lines. The example above creates a new empty workbook.

If you already have a specific workbook you want to open, you would alter the last line, like in this example:

Set objExcel = CreateObject("Excel.Application")
objExcel.visible=True
Set objWB = objExcel.Workbooks.open("C:\MyExcelFile.xlsx")

 

by Beginner (5 points)
Thank you for the quick response!! I follow your code but keep getting this same error; Line: 1 Char: 1, Error: A Microsoft Software Installer error was encountered, Code: 80004023 Source: (null). Have researched Code, tried all suggestions including re-installing Win 7 to 32-bit. To date I've not seen or encountered any install errors, Again, thanks for your help, certainly appreciated!
by Super Expert (2.7k points)

That error normally happens when you update your Excel version from 2010 to some other version. The uninstaller for 64-bit Office doesn't properly clear the registry, so you need to rename a registry entry. The response by Juergen Weber in this thread explains how to resolve it. It basically says you need to rename the registry key

HKEY_CLASSES_ROOT\CLSID\{00024500-0000-0000-C000-000000000046}

to re-enable office automation for Excel. These registry tweaks can be dangerous so do it at your own risk.

Here are the steps to get to the registry and which Office applications correspond to which CLSID key values.

  1. Click Start, and then click Run.
  2. Type regedit, and then click OK.
  3. Move to the HKEY_CLASSES_ROOT\CLSID key. 

    The CLSIDs for the registered automation servers on the system are under this key.
  4. Use the following values of the CLSID key to find the key that represents the Office application that you want to automate. Examine the LocalServer32 key of the CLSID key for the path.
    Office ServerCLSID key
    Access.Application{73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9}
    Excel.Application{00024500-0000-0000-C000-000000000046}
    FrontPage.Application{04DF1015-7007-11D1-83BC-006097ABE675}
    Outlook.Application{0006F03A-0000-0000-C000-000000000046}
    PowerPoint.Application{91493441-5A91-11CF-8700-00AA0060263B}
    Word.Application{000209FF-0000-0000-C000-000000000046}
These instructions are extracted from a response in the thread linked above.
by Beginner (5 points)
Let me first thank you again for the quick response!! I am very sure this will resolve my issue but didn't want to go into REGEDIT unless last resort. So, instead I un-installed Microsoft Office 32-bit and re-installed as 64-bit. That resolved my problem. Your research has helped me to think a little bit more, all credit for resolution goes to you. One last thing, how do I officially mark as SOLVED?
by Super Expert (2.7k points)
Awesome! I'm really glad you got it working. I'm excited to where your Excel studies take you! I marked the post solved :-)

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...