Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.basic.visual.misc > #2294 > unrolled thread
| Started by | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| First post | 2016-12-03 10:31 -0600 |
| Last post | 2016-12-06 00:30 -0500 |
| Articles | 9 — 3 participants |
Back to article view | Back to comp.lang.basic.visual.misc
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
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-03 10:31 -0600 |
| Subject | TroubleShooting Help VBA/Excel |
| Message-ID | <o1uru1$o0i$1@dont-email.me> |
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
[toc] | [next] | [standalone]
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-03 11:11 -0600 |
| Message-ID | <o1uu9q$1cv$1@dont-email.me> |
| In reply to | #2294 |
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
Removed the : from the :=...at least it compiles now
[toc] | [prev] | [next] | [standalone]
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-03 16:46 -0600 |
| Message-ID | <o1vhto$70a$1@dont-email.me> |
| In reply to | #2294 |
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
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.
[toc] | [prev] | [next] | [standalone]
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-04 08:51 -0600 |
| Message-ID | <o21ae9$21i$1@dont-email.me> |
| In reply to | #2294 |
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.
[toc] | [prev] | [next] | [standalone]
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-04 09:19 -0600 |
| Message-ID | <o21c2v$8cc$1@dont-email.me> |
| In reply to | #2297 |
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
[toc] | [prev] | [next] | [standalone]
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-05 08:25 -0600 |
| Message-ID | <o23t9f$43j$1@dont-email.me> |
| In reply to | #2298 |
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
[toc] | [prev] | [next] | [standalone]
| From | john@jeasonNoSpam.cix.co.uk (John K.Eason) |
|---|---|
| Date | 2016-12-06 00:18 +0000 |
| Message-ID | <memo.20161206001826.6468A@jeason.cix.co.uk> |
| In reply to | #2299 |
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.
--
Regards
John (john@jeasonNoSpam.cix.co.uk) Remove the obvious to reply...
[toc] | [prev] | [next] | [standalone]
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Date | 2016-12-05 19:23 -0600 |
| Message-ID | <o253s8$jve$1@dont-email.me> |
| In reply to | #2300 |
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.
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2016-12-06 00:30 -0500 |
| Message-ID | <o25ibb$fkt$1@dont-email.me> |
| In reply to | #2300 |
> 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
[toc] | [prev] | [standalone]
Back to top | Article view | comp.lang.basic.visual.misc
csiph-web