Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: TroubleShooting Help VBA/Excel

Show all headers | View raw

On 12/4/2016 8:51 AM, Gary Scott wrote:
> On 12/3/2016 10:31 AM, Gary Scott wrote:
>> 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

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | 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