Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBS Script and ClearContents -- Code Throws An Error
Need VBS script to clear the contents for range all worksheets in workbook.
Must run as VBS script. Having issue with line "currentWorkSheet .Range( cellRange ).ClearContents"; error generated excelPath = "<Enter a UNC path to a workbook with 1+ worksheets" cellRange = "A1:Z100" WScript.Echo "Reading Data from " & excelPath WScript.Echo "cellRange = " & cellRange Set oExcel = CreateObject("excel.application") oExcel.Workbooks.open( excelPath ) workSheetCount = oExcel.Worksheets.Count WScript.Echo "We have " & workSheetCount & " worksheets" For counter = 1 to workSheetCount WScript.Echo "Reading data from worksheet " & counter & vbCRLF Set currentWorkSheet = oExcel.ActiveWorkbook.Worksheets( counter ) WScript.Echo "Call to Range method next" & vbCrLf currentWorkSheet .Range( cellRange ).ClearContents ' <-- Fails Set currentWorkSheet = Nothing Next oExcel.Workbooks(1).Close oExcel.Quit Set currentWorkSheet = Nothing Set oExcel = Nothing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBS Script and ClearContents -- Code Throws An Error
I changed it a little:
(Created Exc_WB, and there was a space between currentWorksheet and .Range) '----------------------------------------------------------------------------- excelPath = "D:\test.xls" cellRange = "A1:Z100" WScript.Echo "Reading Data from " & excelPath WScript.Echo "cellRange = " & cellRange Set oExcel = CreateObject("excel.application") Set Exc_Wb = oExcel.Workbooks.open( excelPath ) workSheetCount = Exc_Wb.worksheets.Count WScript.Echo "We have " & workSheetCount & " worksheets" For counter = 1 to workSheetCount WScript.Echo "Reading data from worksheet " & counter & vbCRLF Set currentWorkSheet = Exc_Wb.Worksheets( counter ) WScript.Echo "Call to Range method next" & vbCrLf currentWorkSheet.Range( cellRange ).ClearContents ' <-- Fails Next Exc_Wb.Close(True) oExcel.Quit WScript.Echo "finished" '----------------------------------------------------------------------------- test worked hth Carlo On Nov 15, 9:58 am, garibaldi wrote: Need VBS script to clear the contents for range all worksheets in workbook. Must run as VBS script. Having issue with line "currentWorkSheet .Range( cellRange ).ClearContents"; error generated excelPath = "<Enter a UNC path to a workbook with 1+ worksheets" cellRange = "A1:Z100" WScript.Echo "Reading Data from " & excelPath WScript.Echo "cellRange = " & cellRange Set oExcel = CreateObject("excel.application") oExcel.Workbooks.open( excelPath ) workSheetCount = oExcel.Worksheets.Count WScript.Echo "We have " & workSheetCount & " worksheets" For counter = 1 to workSheetCount WScript.Echo "Reading data from worksheet " & counter & vbCRLF Set currentWorkSheet = oExcel.ActiveWorkbook.Worksheets( counter ) WScript.Echo "Call to Range method next" & vbCrLf currentWorkSheet .Range( cellRange ).ClearContents ' <-- Fails Set currentWorkSheet = Nothing Next oExcel.Workbooks(1).Close oExcel.Quit Set currentWorkSheet = Nothing Set oExcel = Nothing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBS Script and ClearContents -- Code Throws An Error
This worked fine for me
excelPath = "<Enter a UNC path to a workbook with 1+ worksheets" cellRange = "A1:Z100" WScript.Echo "Reading Data from " & excelPath WScript.Echo "cellRange = " & cellRange Set oExcel = CreateObject("excel.application") Set oWB = oExcel.Workbooks.Open(excelPath) workSheetCount = oWB.Worksheets.Count WScript.Echo "We have " & workSheetCount & " worksheets" For counter = 1 To workSheetCount WScript.Echo "Reading data from worksheet " & counter & vbCrLf Set currentWorkSheet = oWB.Worksheets(counter) WScript.Echo "Call to Range method next" & vbCrLf currentWorkSheet.Range(cellRange).ClearContents ' <-- Fails Next oWB.Save oWB.Close oExcel.Quit Set currentWorkSheet = Nothing Set oWB = Nothing Set oExcel = Nothing -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "garibaldi" wrote in message ... Need VBS script to clear the contents for range all worksheets in workbook. Must run as VBS script. Having issue with line "currentWorkSheet .Range( cellRange ).ClearContents"; error generated excelPath = "<Enter a UNC path to a workbook with 1+ worksheets" cellRange = "A1:Z100" WScript.Echo "Reading Data from " & excelPath WScript.Echo "cellRange = " & cellRange Set oExcel = CreateObject("excel.application") oExcel.Workbooks.open( excelPath ) workSheetCount = oExcel.Worksheets.Count WScript.Echo "We have " & workSheetCount & " worksheets" For counter = 1 to workSheetCount WScript.Echo "Reading data from worksheet " & counter & vbCRLF Set currentWorkSheet = oExcel.ActiveWorkbook.Worksheets( counter ) WScript.Echo "Call to Range method next" & vbCrLf currentWorkSheet .Range( cellRange ).ClearContents ' <-- Fails Set currentWorkSheet = Nothing Next oExcel.Workbooks(1).Close oExcel.Quit Set currentWorkSheet = Nothing Set oExcel = Nothing |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBS Script and ClearContents -- Code Throws An Error
Hello,
Carlo is right. The error is caused by the space between "currentWorkSheet" and ".Range". Please remove the space and try it again. It also works well on my side. Please let us know if you have any other concerns, or need anything else. Sincerely, Jialiang Ge , remove ¡®online.¡¯) Microsoft Online Community Support ================================================== For MSDN subscribers whose posts are left unanswered, please check this document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.aspx#notif ications. If you are using Outlook Express/Windows Mail, please make sure you clear the check box ¡°Tools/Options/Read: Get 300 headers at a time¡± to see your reply promptly. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscripti...t/default.aspx. ================================================== This posting is provided ¡°AS IS¡± with no warranties, and confers no rights. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBS Script and ClearContents -- Code Throws An Error
Thanks to all...
-- G "garibaldi" wrote: Need VBS script to clear the contents for range all worksheets in workbook. Must run as VBS script. Having issue with line "currentWorkSheet .Range( cellRange ).ClearContents"; error generated excelPath = "<Enter a UNC path to a workbook with 1+ worksheets" cellRange = "A1:Z100" WScript.Echo "Reading Data from " & excelPath WScript.Echo "cellRange = " & cellRange Set oExcel = CreateObject("excel.application") oExcel.Workbooks.open( excelPath ) workSheetCount = oExcel.Worksheets.Count WScript.Echo "We have " & workSheetCount & " worksheets" For counter = 1 to workSheetCount WScript.Echo "Reading data from worksheet " & counter & vbCRLF Set currentWorkSheet = oExcel.ActiveWorkbook.Worksheets( counter ) WScript.Echo "Call to Range method next" & vbCrLf currentWorkSheet .Range( cellRange ).ClearContents ' <-- Fails Set currentWorkSheet = Nothing Next oExcel.Workbooks(1).Close oExcel.Quit Set currentWorkSheet = Nothing Set oExcel = Nothing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Throws Error 91 | Excel Programming | |||
workbook.open throws 1004 error | Excel Programming | |||
How do I perform a certain function if VBA throws up an error? | Excel Programming | |||
[Q] Save As throws type mismatch error in control's code? | Excel Discussion (Misc queries) | |||
Format statement no longer throws an error | Excel Programming |