Groups > comp.lang.basic.visual.misc > #2294

TroubleShooting Help VBA/Excel

From	Gary Scott <garylscott@sbcglobal.net>
Newsgroups	comp.lang.basic.visual.misc
Subject	TroubleShooting Help VBA/Excel
Date	2016-12-03 10:31 -0600
Organization	A noiseless patient Spider
Message-ID	<o1uru1$o0i$1@dont-email.me> (permalink)

Show all headers | View raw

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

Back to comp.lang.basic.visual.misc | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-03 10:31 -0600
  Re: TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-03 11:11 -0600
  Re: TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-03 16:46 -0600
  Re: TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-04 08:51 -0600
    Re: TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-04 09:19 -0600
      Re: TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-05 08:25 -0600
        Re: TroubleShooting Help VBA/Excel john@jeasonNoSpam.cix.co.uk (John K.Eason) - 2016-12-06 00:18 +0000
          Re: TroubleShooting Help VBA/Excel Gary Scott <garylscott@sbcglobal.net> - 2016-12-05 19:23 -0600
          Re: TroubleShooting Help VBA/Excel GS <gs@v.invalid> - 2016-12-06 00:30 -0500

csiph-web