Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.basic.visual.misc > #2295
| From | Gary Scott <garylscott@sbcglobal.net> |
|---|---|
| Newsgroups | comp.lang.basic.visual.misc |
| Subject | Re: TroubleShooting Help VBA/Excel |
| Date | 2016-12-03 11:11 -0600 |
| Organization | A noiseless patient Spider |
| Message-ID | <o1uu9q$1cv$1@dont-email.me> (permalink) |
| References | <o1uru1$o0i$1@dont-email.me> |
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
Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll 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