Error 424 for sample code?

[Taras_96]

Hi all,

I'm new to VBA programming, and I'm trying to run the code found at:
http://www.microsoft.com/technet/scriptcenter/resources/officetips/may05/tips0503.mspx

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "Olympia, WA"
objExcel.Cells(2, 1).Value = "Salem, OR"
objExcel.Cells(3, 1).Value = "Boise, ID"
objExcel.Cells(4, 1).Value = "Sacramento, CA"

Set objRange = objExcel.Range("A1").EntireColumn
Set objRange2 = objExcel.Range("B1")

objRange.TextToColumns objRange2,,,,,,TRUE

When I click on run, I get a 424 error, and clicking on debug sends me
to the last line. What is wrong with the code?

Thanks

Taras

 

[Dave Peterson]

The code worked ok for me (xl2003).

But I would have used:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = "Olympia, WA"
objWorksheet.Cells(2, 1).Value = "Salem, OR"
objWorksheet.Cells(3, 1).Value = "Boise, ID"
objWorksheet.Cells(4, 1).Value = "Sacramento, CA"

Set objRange = objWorksheet.Range("A1").EntireColumn
Set objRange2 = objWorksheet.Range("B1")

objRange.TextToColumns objRange2, , , , , , True

 

[Rick Rothstein \(MVP - VB\)]

I'd like to point out for the OP that the webpage where he got that code was
for VBScript (not VBA) which is what necessitated the need to create the
Excel application (the first 4 lines of code). When performing the same
functionality inside of Excel, you can execute the code directly within the
Excel session you are currently in (and eliminate the first four lines of
code and, as an aside, we can eliminate the objRange and objRange2 Set
statements by working with the given Ranges directly). So, assuming the OP
did not really need to create a new workbook for some other reason, I would
have written the code something like this...

With Worksheets("Sheet1")
.Cells(1, 1).Value = "Olympia, WA"
.Cells(2, 1).Value = "Salem, OR"
.Cells(3, 1).Value = "Boise, ID"
.Cells(4, 1).Value = "Sacramento, CA"
.Range("A1").EntireColumn.TextToColumns .Range("B1"), , , , , , True
End With

so the only significant code to pay attention to is that last line inside
the With/End With block (the rest simply creating the data for that last
line to operate on). I would also note that all of the state abbreviations
were placed in Column C with leading space characters, so code to eliminate
them would probably be necessary later on.

Rick

 

[Taras_96]

Hmmm, now it works fine ?! Thanks for the tips regarding the creation
of the new excel document - you were correct in guessing that I didn't
want to create a new worksheet

Taras