VBScript Opening folder to convert csv to xls

Question

Can anyone explain why this isn't working, its throwing an error saying:

Line: 14 Char: 1 Error: Object Required:'[string: "H:\VBS"]' Code: 800A01A8

Const xlDelimited = 1
Const xlNormal = -4143 

Set Excel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

Dim Excel
dim objShell
dim objFolder
Dim fileLocation

set objShell = CreateObject("shell.application")
set objFolder = objshell.BrowseForFolder(0, "Select File Location", 1, "H:\")
set fileLocation = CStr(objFolder.self.path)

Set Excel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

folderDestination = InputBox("Enter Move to folder")
For Each f In fso.GetFolder(fileLocation).Files
  If LCase(fso.GetExtensionName(f)) = "csv" Then
        .Workbooks.Open
    .Sheets(1).Columns("A").TextToColumns .Range("A1"), xlDelimited,,,,,,,True,"|"
    .ActiveWorkbook.SaveAs .ActiveWorkbook.Path & "\1.xls", xlNormal
    .Quit
  End If
Next

Quite confused to say the least :S Once again any help is much appreciated!

this `set fileLocation = CStr(objFolder.self.path)` fails as you are trying to assign object to a string. Remove `set` — , Aug 12 '13 at 12:24
What's `folderDestination` doing there? It's never used. Also you're creating an Excel application twice, but you need only one. There's no need to quit Excel after processing one file. Recommended reading: [What does the keyword Set actually do in VBA?](http://stackoverflow.com/q/349613/18771) — Tomalak, Aug 12 '13 at 12:25
folderDestination is being used later once I pass this hurdle, also im not using VBA. — Nathan Hawthorne, Aug 12 '13 at 12:35
...not to mention the three lines of code starting with `.Workbooks.Open`, which are obviously missing an enclosing `With` block. If I may give you the one piece of solid advice that you should frame and hang above the bed: **Don't copy random code off the internet. Especially code you don't understand.** Write every line yourself, *after* you are certain what it does. — Tomalak, Aug 12 '13 at 12:38
VBScript and VBA are syntactically equivalent. Things true for VBA are true for VBS. — Tomalak, Aug 12 '13 at 12:59

score 2 · Answer 1 · answered Aug 12 '13 at 12:17

2

"CStr(objFolder.self.path)" evaluates to a string, so you should not use Set (used for objects only) in the assigment.

answered Aug 12 '13 at 12:17

Ekkehard.Horner

38,498
2
45
96

score 0 · Answer 2 · answered Aug 12 '13 at 14:40

Try this:

Const xlDelimited = 1

Const xlNormal = -4143 


Set Excel = CreateObject("Excel.Application")

Set fso = CreateObject("Scripting.FileSystemObject")


Dim Excel

dim objShell

dim objFolder

Dim fileLocation


set objShell = CreateObject("shell.application")

set objFolder = objshell.BrowseForFolder(0, "Select File Location", 1, "H:\")

set fileLocation = fso.GetFolder(objFolder.self.path)

Set Excel = CreateObject("Excel.Application")

Set fso = CreateObject("Scripting.FileSystemObject")

folderDestination = InputBox("Enter Move to folder")

For Each f In fso.GetFolder(fileLocation).Files

  If LCase(fso.GetExtensionName(f)) = "csv" Then

        .Workbooks.Open

    .Sheets(1).Columns("A").TextToColumns .Range("A1"), xlDelimited,,,,,,,True,"|"

    .ActiveWorkbook.SaveAs .ActiveWorkbook.Path & "\1.xls", xlNormal

    .Quit

  End If

Next

VBScript Opening folder to convert csv to xls

2 Answers2