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

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

Re: TroubleShooting Help VBA/Excel

Show all headers | View raw

This line is not required below since the replace addresses the usedrange

objExcel.ActiveSheet.UsedRange.Select



On 12/4/2016 9:19 AM, Gary Scott wrote:
> 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