Archive

Posts Tagged ‘Excel’

Gotcha: VBScript DropHandler doesn’t support Unicode filenames

It seems VBScript (.vbs) script file’s DropHandler on Windows is broken regarding Unicode filenames, as described at https://stackoverflow.com/a/4366906/903783

A workaround suggested by user DG there (https://stackoverflow.com/a/13587850/903783) was to use a batch file to accept the dropped file, then pass the filename(s) to the VBScript script file (.vbs) as command-line parameters. If you just care about a single instead of multiple dropped files, there’s a simpler version of a batch file one can use, as I suggested at https://stackoverflow.com/a/52239049/903783. Copying below:


Based on DG’s answer, if you just want to accept one file as drop target then you can write a batch file (if you have it named as "x.bat" place VBscript with filename "x.bat.vbs" at same folder) that just contains:

@"%0.vbs" %1

the @ means to not output the row on the display (I found it to show garbage text even if you use chcp 1250 as first command)

don’t use double-quotes around %1, it won’t work if your VBScript uses logic like the following (code I was using below was from http://jeffkinzer.blogspot.com/2012/06/vbscript-to-convert-excel-to-csv.html). Tested it and it works fine with spaces in the file and folder names:

  Dim strExcelFileName
  strExcelFileName = WScript.Arguments.Item(0) 'file name to parse

  ' get path where script is running
  strScript = WScript.ScriptFullName
  Dim fso
  Set fso = CreateObject ("Scripting.FileSystemObject") 
  strScriptPath = fso.GetAbsolutePathName(strScript & "\..")
  Set fso = Nothing

  ' If the Input file is NOT qualified with a path, default the current path
  LPosition = InStrRev(strExcelFileName, "\")
  if LPosition = 0 Then 'no folder path
    strExcelFileName = strScriptPath & "\" & strExcelFileName
    strScriptPath = strScriptPath & "\"
  else 'there is a folder path, use it for the output folder path also
    strScriptPath = Mid(strExcelFileName, 1, LPosition)
  End If
  ' msgbox LPosition & " - " & strExcelFileName & " - " & strScriptPath
Advertisements

Gotcha: Worksheets property is read-only, Sheets is not – Excel Workbook

My contribution to:

http://stackoverflow.com/questions/14109102/how-do-i-add-a-worksheet-after-all-existing-excel-worksheets

 

Seems Worksheets property is read-only

Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

https://msdn.microsoft.com/en-us/library/office/ff835542(v=office.15).aspx

whereas Sheets is the real thing where you can also add Sheets dynamically

A collection of all the sheets in the specified or active workbook.

https://msdn.microsoft.com/en-us/library/office/ff193217(v=office.15).aspx

Fix: embed Office & Acrobat Active Documents in IE WebBrowser control

Since the LvS desktop application (from Learning Via Subtitling project) uses Internet Explorer to embed Microsoft Office Word, Excel, PowerPoint and Adobe Acrobat PDF documents (among others), I added some useful info to the LvS download page on how to work around issues one may face in such a scenario:

If you don’t have Microsoft Office (or have very old Office versions like Office 95 or Office 97) and want to embed Word documents you should better also install:

To show .docx (Word 2007+) files if you use Office XP or Office 2003 or the free Word viewer you need to also install "Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats" from: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3

Office 2007 doesn’t show documents embedded in Internet Explorer by default (which LvS uses internally to host Active Documents), use FixIt button at following page to fix this: http://support.microsoft.com/?id=927009 

In LvS if you have Office 2007 without this fix you will see message "Type the correct address" when your unpacked activity folder contains Office files

To embed Adobe Acrobat files you need Adobe Acrobat or the free Adobe Acrobat Reader from: http://adobe.com/reader

If Adobe PDF files don’t show embedded and open up in separate window: Use Start/All Programs menu from the Windows Taskbar and run Adobe Reader application. Then go to its menu "Edit/Preferences…" and at the dialog that opens up, go to "Internet" and check "Display PDF in browser", then press OK (can then close Adobe Reader).

When such files open embedded you may be asked to open or save the file for each one – select open and DO check to not be asked again (since it gets very annoying).

Since this "not ask again" will be remembered for IE too, if you want to clear it later on see the following article: http://www.howtogeek.com/howto/windows-vista/reset-opensave-choice-for-internet-explorer-downloads-in-vista/

%d bloggers like this: