Gary Scott
2016-12-03 16:31:11 UTC
I'm trying to find and replace only specifically those cells that some
inconsiderate file producer has selected and forced a display state with
number format and zero decimal places. Unfortunately, the file has
dates that I want to retain as date values on export. So I thought I
would try find/replace. It chokes on the replace (line 13) below
(according to the VB error message). I tried replacing Cells with
Selection. Suggestions?
Const xlText = -4158
Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count - 1
FullName = objArgs(I)
FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )
Set objExcel = CreateObject("Excel.application")
Set objExcelBook = objExcel.Workbooks.Open(FullName)
If objExcel.Application.ActiveSheet.Name = "Cover Page" Then
objExcel.application.Worksheets(2).Activate
If objExcel.Application.ActiveSheet.Name = "Disclosure Page" Then
objExcel.application.Worksheets(2).Activate
objExcel.Application.Cells.Select
objExcel.Application.FindFormat.NumberFormat = "Number"
objExcel.Application.ReplaceFormat.NumberFormat = "General"
objExcel.Application.Cells.Replace What:="", Replacement:="",
SearchFormat:=True, ReplaceFormat:=True
objExcelBook.SaveAs FileName & "tsv", xlText
objExcel.Application.Quit
objExcel.Quit
Set objExcel = Nothing
Set objExcelBook = Nothing
Next
inconsiderate file producer has selected and forced a display state with
number format and zero decimal places. Unfortunately, the file has
dates that I want to retain as date values on export. So I thought I
would try find/replace. It chokes on the replace (line 13) below
(according to the VB error message). I tried replacing Cells with
Selection. Suggestions?
Const xlText = -4158
Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count - 1
FullName = objArgs(I)
FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )
Set objExcel = CreateObject("Excel.application")
Set objExcelBook = objExcel.Workbooks.Open(FullName)
If objExcel.Application.ActiveSheet.Name = "Cover Page" Then
objExcel.application.Worksheets(2).Activate
If objExcel.Application.ActiveSheet.Name = "Disclosure Page" Then
objExcel.application.Worksheets(2).Activate
objExcel.Application.Cells.Select
objExcel.Application.FindFormat.NumberFormat = "Number"
objExcel.Application.ReplaceFormat.NumberFormat = "General"
objExcel.Application.Cells.Replace What:="", Replacement:="",
SearchFormat:=True, ReplaceFormat:=True
objExcelBook.SaveAs FileName & "tsv", xlText
objExcel.Application.Quit
objExcel.Quit
Set objExcel = Nothing
Set objExcelBook = Nothing
Next