Discussion:
TroubleShooting Help VBA/Excel
(too old to reply)
Gary Scott
2016-12-03 16:31:11 UTC
Permalink
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
Gary Scott
2016-12-03 17:11:38 UTC
Permalink
Post by Gary Scott
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
Removed the : from the :=...at least it compiles now
Gary Scott
2016-12-03 22:46:33 UTC
Permalink
Post by Gary Scott
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
Replaced NumberFormat with "#,##0" or "0"
Replaced ReplaceFormat with ""#,##0.00"

and removed text change, added clearformat to no avail. I can query
cell by cell and validate the format as expected, but replace fails to
find anything to change.
Gary Scott
2016-12-04 14:51:07 UTC
Permalink
Post by Gary Scott
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
Solved, cells.replace doesn't like the keyword argument form (What=).
If I replace the keyword form with a simple argument list of values ("
", " ", 2, 1, false, false, false, false) it works.

Can someone summarize what I'm seeing? Function call versus method
call? wscript vs cscript? Clearly this isn't the VB replace function.
Gary Scott
2016-12-04 15:19:11 UTC
Permalink
Post by Gary Scott
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
Solved, cells.replace doesn't like the keyword argument form (What=). If
I replace the keyword form with a simple argument list of values (" ", "
", 2, 1, false, false, false, false) it works.
Can someone summarize what I'm seeing? Function call versus method
call? wscript vs cscript? Clearly this isn't the VB replace function.
Final...you can pretty it up a bit, more named constants. This is
intended to 1) ensure correct worksheet is selected, 2) change all
numerical values to restore precision (some dummy inexplicably rounded
to whole numbers), 3) export to tab delimited form.

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)
objExcel.application.visible=false
objExcel.application.displayalerts=false
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.ActiveSheet.UsedRange.Select
objExcel.FindFormat.Clear
objExcel.ReplaceFormat.Clear
objExcel.FindFormat.NumberFormat="#,##0"
objExcel.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False, False, True,
True
objExcel.Application.FindFormat.NumberFormat="0"
objExcel.Application.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False, False, True,
True
objExcelBook.SaveAs FileName & "tsv", xlText
objExcel.Application.Quit
objExcel.Quit
Set objExcel = Nothing
Set objExcelBook = Nothing
Next
Gary Scott
2016-12-05 14:25:04 UTC
Permalink
This line is not required below since the replace addresses the usedrange

objExcel.ActiveSheet.UsedRange.Select
Post by Gary Scott
Post by Gary Scott
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
Solved, cells.replace doesn't like the keyword argument form (What=). If
I replace the keyword form with a simple argument list of values (" ", "
", 2, 1, false, false, false, false) it works.
Can someone summarize what I'm seeing? Function call versus method
call? wscript vs cscript? Clearly this isn't the VB replace function.
Final...you can pretty it up a bit, more named constants. This is
intended to 1) ensure correct worksheet is selected, 2) change all
numerical values to restore precision (some dummy inexplicably rounded
to whole numbers), 3) export to tab delimited form.
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)
objExcel.application.visible=false
objExcel.application.displayalerts=false
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.ActiveSheet.UsedRange.Select
objExcel.FindFormat.Clear
objExcel.ReplaceFormat.Clear
objExcel.FindFormat.NumberFormat="#,##0"
objExcel.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False, False, True,
True
objExcel.Application.FindFormat.NumberFormat="0"
objExcel.Application.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False, False, True,
True
objExcelBook.SaveAs FileName & "tsv", xlText
objExcel.Application.Quit
objExcel.Quit
Set objExcel = Nothing
Set objExcelBook = Nothing
Next
John K.Eason
2016-12-06 00:18:00 UTC
Permalink
*Date:* Mon, 5 Dec 2016 08:25:04 -0600
This line is not required below since the replace addresses the usedrange
objExcel.ActiveSheet.UsedRange.Select
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
I'm trying to find and replace only specifically those cells
that some
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
inconsiderate file producer has selected and forced a display
state with
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
number format and zero decimal places. Unfortunately, the file
has
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
dates that I want to retain as date values on export. So I
thought I
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
would try find/replace. It chokes on the replace (line 13)
below
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
(according to the VB error message). I tried replacing Cells
with
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
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
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
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
Solved, cells.replace doesn't like the keyword argument form
(What=). If
Post by Gary Scott
Post by Gary Scott
I replace the keyword form with a simple argument list of values
(" ", "
Post by Gary Scott
Post by Gary Scott
", 2, 1, false, false, false, false) it works.
Can someone summarize what I'm seeing? Function call versus
method
Post by Gary Scott
Post by Gary Scott
call? wscript vs cscript? Clearly this isn't the VB replace
function.
Post by Gary Scott
Final...you can pretty it up a bit, more named constants. This is
intended to 1) ensure correct worksheet is selected, 2) change all
numerical values to restore precision (some dummy inexplicably rounded
to whole numbers), 3) export to tab delimited form.
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)
objExcel.application.visible=false
objExcel.application.displayalerts=false
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.ActiveSheet.UsedRange.Select
objExcel.FindFormat.Clear
objExcel.ReplaceFormat.Clear
objExcel.FindFormat.NumberFormat="#,##0"
objExcel.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False,
False, True,
True
objExcel.Application.FindFormat.NumberFormat="0"
objExcel.Application.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False,
False, True,
True
objExcelBook.SaveAs FileName & "tsv", xlText
objExcel.Application.Quit
objExcel.Quit
Set objExcel = Nothing
Set objExcelBook = Nothing
Next
You're posting in the wrong newsgroup. This group is for classic Visual Basic (VB5,
VB6), not Office VBA.
--
Regards
John (***@jeasonNoSpam.cix.co.uk) Remove the obvious to reply...
Gary Scott
2016-12-06 01:23:37 UTC
Permalink
Post by John K.Eason
*Date:* Mon, 5 Dec 2016 08:25:04 -0600
This line is not required below since the replace addresses the usedrange
objExcel.ActiveSheet.UsedRange.Select
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
I'm trying to find and replace only specifically those cells
that some
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
inconsiderate file producer has selected and forced a display
state with
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
number format and zero decimal places. Unfortunately, the file
has
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
dates that I want to retain as date values on export. So I
thought I
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
would try find/replace. It chokes on the replace (line 13)
below
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
(according to the VB error message). I tried replacing Cells
with
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
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
Post by Gary Scott
Post by Gary Scott
Post by Gary Scott
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
Solved, cells.replace doesn't like the keyword argument form
(What=). If
Post by Gary Scott
Post by Gary Scott
I replace the keyword form with a simple argument list of values
(" ", "
Post by Gary Scott
Post by Gary Scott
", 2, 1, false, false, false, false) it works.
Can someone summarize what I'm seeing? Function call versus
method
Post by Gary Scott
Post by Gary Scott
call? wscript vs cscript? Clearly this isn't the VB replace
function.
Post by Gary Scott
Final...you can pretty it up a bit, more named constants. This is
intended to 1) ensure correct worksheet is selected, 2) change all
numerical values to restore precision (some dummy inexplicably rounded
to whole numbers), 3) export to tab delimited form.
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)
objExcel.application.visible=false
objExcel.application.displayalerts=false
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.ActiveSheet.UsedRange.Select
objExcel.FindFormat.Clear
objExcel.ReplaceFormat.Clear
objExcel.FindFormat.NumberFormat="#,##0"
objExcel.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False,
False, True,
True
objExcel.Application.FindFormat.NumberFormat="0"
objExcel.Application.ReplaceFormat.NumberFormat="#,##0.00"
objExcel.ActiveSheet.UsedRange.Replace "", "", 2, 1, False,
False, True,
True
objExcelBook.SaveAs FileName & "tsv", xlText
objExcel.Application.Quit
objExcel.Quit
Set objExcel = Nothing
Set objExcelBook = Nothing
Next
You're posting in the wrong newsgroup. This group is for classic Visual Basic (VB5,
VB6), not Office VBA.
I searched through about a half dozen and picked one that seemed to
actually be used..."misc" stands for miscellaneous. The term
miscellaneous does not imply such restriction.
GS
2016-12-06 05:30:36 UTC
Permalink
Post by John K.Eason
You're posting in the wrong newsgroup. This group is for classic Visual Basic (VB5,
VB6), not Office VBA.
While the OP's Q is Excel-specific.., I assume he's automating an
instance of Excel with (looks to be) WScript. Your comment applies just
the same, though, and should be posted in aan Excel newsgroup.

To Gary: ask here...

microsoft.public.excel.programming

..for better help!
--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Loading...