Block input in VBS?

[Piglette]

objWorksheet.Paste works fine but there is the possibility that the user could corrupt the Clipboard at a critical moment.

Is there a VBS solution to disable, then enable mouse/keyboard input (like the AutoHotkey BlockInput ON/OFF)?

Thanks

Lukas

[HansV]

As far as I know, there is no "purely VBScript" solution since you can't use Windows API calls there.

If you can install AutoIt (free) on all PCs where your script will be run, see the suggestion in disable/enable keyboard.

[rory]

What/where are you copying from?

[Piglette]

Hans and Rory:

I am inserting a tab-delimited text file into an Excel 2007 spreadsheet.

This is slow (but bulletproof) when there are 35 columns and 100,000 rows:

for Irow = 0 to ubound(arrayX)
TL = split(arrayX(Irow),chr(09))
for Icol = 0 to 34
objWorksheet.Cells(Irow+2,Icol+1) = TL(Icol)
next
next

Pre-loading the Clipboard and ObjWorksheet.Paste is far faster.

My solution to the possibility of the user overriding the Clipboard contents is to "Blockinput On" from the AutoHotkey calling program
Then we invoke the VBScript portion that handles the Excel
Immediately after the ObjWorksheet.Paste, the vbs writes a marker file which the calling ahk has been waiting for
At which point we BlockInput Off

And the user will be none the wiser other than a few milliseconds where the keyboard and mouse are unresponsive.

Thanks

Lukas

[HansV]

You can insert a text file in one go:

Code: Select all

' Modify as needed
strFile = "C:\Test.txt"
With objWorksheet.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=objWorksheet.Range("$A$1"))
  .TextFileStartRow = 1
  .TextFileParseType = 1 ' xlDelimited
  .TextFileTextQualifier = 1 ' xlTextQualifierDoubleQuote
  .TextFileTabDelimiter = True
  .Refresh BackgroundQuery:=False
  .Delete
End With

[Piglette]

Hans:

That looks like a much better solution. However, I am getting a VBS compilation error

"Expected ')'"

at char 45 of the With line

I have not been able to find any helpful documentation regarding this command.

Thanks for all your help.

Lukas

[HansV]

I apologize, my bad. I only tested in Excel itself, not in VBScript. VBScript doesn't support named arguments such as Connection:=, so you have to change the code to

Code: Select all

' Modify as needed
strFile = "C:\Test.txt"
With objWorksheet.QueryTables.Add("TEXT;" & strFile, objWorksheet.Range("$A$1"))
  .TextFileStartRow = 1
  .TextFileParseType = 1 ' xlDelimited
  .TextFileTextQualifier = 1 ' xlTextQualifierDoubleQuote
  .TextFileTabDelimiter = True
  .Refresh False
  .Delete
End With

[Piglette]

Hans:

Great!

But

Even with cells formatted as text, 16 digit strings in the source txt are being converted to engineering format xxxx+E14

The text file also does contain genuine numbers that we want to retain as such for calculations in the Excel.

Thanks for your patience!

Lukas

[HansV]

You can tell Excel how to interpret each column by adding a line like this after the With objWorksheet ... line:

Code: Select all

  .TextFileColumnDataTypes = Array(2, 1, 2, ...)

The array should contain as many entries as there are columns (so in your example, 35 entries).
The meaning of the numbers is as follows:

1 = xlGeneralFormat: let Excel decide the data type
2 = xlTextFormat: force the value to be imported as text
3 = xlMDYFormat: force the value to be interpreted as a date in US date format M/D/Y
9 = xlSkipColumn: don't import this column

So in the above example, the first column would be imported as text, Excel would determine the data type of the second column, and the third would be text again.

[Piglette]

Very nice!

Do you have a recommended source of documentation for this command? I was unable to find much of anything helpful.

Many thanks again!

Lukas

[HansV]

See the Excel VBA documentation in the offline help or on MSDN:

QueryTable Object
TextFileColumnDataTypes Property