Reputation: 53
I am trying to import CSV file into MS Excel (2016) using VBA Code. Manipulating import didn't work so far, Excel uses windows system delimiter every time over what I am trying to set in VBA import.
So, I thought to use a VBScript to change system delimiter from 'comma' to 'Tab' at first, import CSV file, then set system delimiter back to 'comma'.
Having VBScript as separate file and running it by:
Shell "wscript D:\Hutsul\ChangeDelimiter.vbs", vbNormalFocus
Works, but VBA needs to run on other machines, and for ease of use I wanted to try and code VBScript in-line within Excel VBA environment.
Code that I have written stops at Set scr = New ScriptControl
and throws:
Run-time Error '429': ActiveX component can't create object
Tried set scr = CreateObject("MSScriptControl.ScriptControl")
didn't help.
Also, here is a snip of my VBAProject References:
Below is my VBA code for in-line script (Separate Script file contains just what's in scriptCode
lines:
'Execute VBScript directly from VBA to change system delimiter from COMMA to TAB
Dim scriptCode As String
Dim scr As ScriptControl: Set scr = New ScriptControl
scriptCode = ""
scriptCode = scriptCode & "Set sh = CreateObject(""WScript.Shell"")"
scriptCode = scriptCode & "Path = ""HKCU\Control Panel\International\sList"""
scriptCode = scriptCode & "Set Separator = CreateObject(""Scripting.Dictionary"")"
scriptCode = scriptCode & "Separator.Add True, ""\t"""
scriptCode = scriptCode & "Separator.Add False, "","""
scriptCode = scriptCode & "sh.RegWrite Path, Separator(sh.RegRead(Path) = "",""), ""REG_SZ"""
scr.Language = "VBScript"
scr.AddCode scriptCode
scr.Run scriptCode
Also, when the script itself is executed (as a separate .vbs file for now) multiple times, it switches delimiter, instead of just setting from 'comma' to 'Tab' and keeping it that way, so if delimiter is already 'Tab' and I run script again, delimiter becomes 'comma' again.
Any help is greatly appreciated, thank you!
Upvotes: 1
Views: 6969
Reputation: 131
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\SOFTWARE\Classes\CLSID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
@="ScriptControl Object"
"AppID"="{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}"
[HKEY_CURRENT_USER\SOFTWARE\Classes\Wow6432Node\AppID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
"DllSurrogate"=""
[HKEY_CURRENT_USER\SOFTWARE\Classes\AppID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
"DllSurrogate"=""
This reg file enables MSScriptControl for x64 programs by forcing MSScript's 32 bit DLLs into the 32 bit DLLHost allowing 64 bit programs to access this 32 bit dll. COM objects in exe files can be accessed by both 64 and 32 bit programs.
As Regedit requires admin permissions to start, if not an Admin use this batch file to merge the reg file as reg.exe doesn't require admin permissions to start. The registry file only contains per user settings which all users are allowed to change (but with reg.exe not regedit.exe).
reg import "%~dp0\MSScript.reg"
Pause
The alternative way is to host the interfaces yourself in a class file. You need to implement IActiveScriptSite ay a minimum. See IActiveScript topic in VBS help http://download.microsoft.com/download/winscript56/Install/5.6/W982KMeXP/EN-US/scrdoc56en.exe
Upvotes: 0
Reputation: 149305
You can run VBScript
on a 64-bit Windows as a 32-bit process or a 64-bit process. I believe you have a 64 bit system and hence when you double click it, it is running it as 32-bit.. resulting in error. If you are running 32-bit, then you need the 32-bit ActiveX and if running 64-bit, then you need the 64-bit ActiveX.
To run a 32-bit VBScript on a 64-bit system, try this
Create a new VBS file called Test.Vbs
and save it at as C:\Temp\Test.Vbs
Type these two lines in that file.
Set scr = CreateObject("MSScriptControl.ScriptControl")
MsgBox "Success"
Now press Win + R to launch Run
. Type this
C:\windows\sysWOW64\cscript C:\Temp\test.vbs
Does it run now? Do you see the message box "Success"?
If the above doesn't run then try this
C:\windows\system32\cscript C:\Temp\test.vbs
One of them will run.
Upvotes: 1