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

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

Re: TroubleShooting Help VBA/Excel

Show all headers | View raw

On 12/5/2016 6:18 PM, John K.Eason wrote:
> In article <o23t9f$43j$1@dont-email.me>, garylscott@sbcglobal.net (Gary Scott)
> wrote:
>
>> *From:* Gary Scott <garylscott@sbcglobal.net>
>> *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
>>
>>
>>
>> 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
>
> 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.

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