Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 for sample code?
Hi all,
I'm new to VBA programming, and I'm trying to run the code found at: http://www.microsoft.com/technet/scr.../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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 for sample code?
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 Taras_96 wrote: Hi all, I'm new to VBA programming, and I'm trying to run the code found at: http://www.microsoft.com/technet/scr.../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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 for sample code?
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 "Dave Peterson" wrote in message ... 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 Taras_96 wrote: Hi all, I'm new to VBA programming, and I'm trying to run the code found at: http://www.microsoft.com/technet/scr.../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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sample code to be fixed! | Excel Discussion (Misc queries) | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
Need sample code | Excel Programming | |||
Registration Key Sample Code | Excel Programming | |||
Need Sample VBA Code | Excel Programming |