how to select columns using macro/vbscript from a readonly excel file 
Author Message
 how to select columns using macro/vbscript from a readonly excel file

i have an application(microstrategy DSS Broadcaster) which exports data to
excel sheet. the appication does the export making excel invisible. once the
export is over , i am using workbook_beforesave event to execute a macro
which formats the data in the excel sheet. what i want is to insert some
rows into the sheet, add some data into the colums, delete some columns and
make some data bold(to do totaling and sub totaling). when i execute this,
all the code is getting executed except the selecting and making it bold,
selecting and inserting rows,deleting rows.  i put the macro in the debug
mode and made the excel sheet visible and i went thru each step of the
code..it goes without any problem , but the rows/cells doesn't get selected.
when i made the excel visisble, the workbook is opened in readonly mode..but
during the execution of the code, when there is a "cells(2,3)="data", it
gets inserted, but when i try to select this cell and make it bold it
doesn't work.
can anybody help me in this.

i tried the same from outside this application(Microstrategy DSS
Broadcaster). the macro (beforesave event) runs perfectly fine selecting
cells,making it bold, inserting rows etc.

Thanks,
Annie Wu



Sat, 08 Jun 2002 03:00:00 GMT  
 how to select columns using macro/vbscript from a readonly excel file
I guess the lesson would be that you can't select anything unless it is
visible and in most cases you don't need to.

Change any code like

Cells(3,5).Select
Selection.EntireRow.Insert

to

Cells(3,5).EntireRow.Insert

Remove all the
Select - selection pairs.

Why post to:
microsoft.public.word.vba.beginners,
microsoft.public.word.vba.customization,
microsoft.public.word.vba.general

if this is an excel problem.  Hopefully you will get back to the groups I
limited my answer to.

HTH,
Tom Ogilvy
MVP Excel

Quote:

>i have an application(microstrategy DSS Broadcaster) which exports data to
>excel sheet. the appication does the export making excel invisible. once
the
>export is over , i am using workbook_beforesave event to execute a macro
>which formats the data in the excel sheet. what i want is to insert some
>rows into the sheet, add some data into the colums, delete some columns and
>make some data bold(to do totaling and sub totaling). when i execute this,
>all the code is getting executed except the selecting and making it bold,
>selecting and inserting rows,deleting rows.  i put the macro in the debug
>mode and made the excel sheet visible and i went thru each step of the
>code..it goes without any problem , but the rows/cells doesn't get
selected.
>when i made the excel visisble, the workbook is opened in readonly
mode..but
>during the execution of the code, when there is a "cells(2,3)="data", it
>gets inserted, but when i try to select this cell and make it bold it
>doesn't work.
>can anybody help me in this.

>i tried the same from outside this application(Microstrategy DSS
>Broadcaster). the macro (beforesave event) runs perfectly fine selecting
>cells,making it bold, inserting rows etc.

>Thanks,
>Annie Wu



Sat, 08 Jun 2002 03:00:00 GMT  
 how to select columns using macro/vbscript from a readonly excel file
Tom,

I am working with annie in this case. we made the excel sheet visible while
running the macro.when we made it visible it opened as read only.  we were
able to insert data into cells, but not able to change the font of the cell
or size etc or to insert rows into it. we also tried saving this workbook as
a different workbook. it doesn't save this as another workbook also.
Thanks,
Cini Sathyavan


Quote:
> I guess the lesson would be that you can't select anything unless it is
> visible and in most cases you don't need to.

> Change any code like

> Cells(3,5).Select
> Selection.EntireRow.Insert

> to

> Cells(3,5).EntireRow.Insert

> Remove all the
> Select - selection pairs.

> Why post to:
> microsoft.public.word.vba.beginners,
> microsoft.public.word.vba.customization,
> microsoft.public.word.vba.general

> if this is an excel problem.  Hopefully you will get back to the groups I
> limited my answer to.

> HTH,
> Tom Ogilvy
> MVP Excel


> >i have an application(microstrategy DSS Broadcaster) which exports data
to
> >excel sheet. the appication does the export making excel invisible. once
> the
> >export is over , i am using workbook_beforesave event to execute a macro
> >which formats the data in the excel sheet. what i want is to insert some
> >rows into the sheet, add some data into the colums, delete some columns
and
> >make some data bold(to do totaling and sub totaling). when i execute
this,
> >all the code is getting executed except the selecting and making it bold,
> >selecting and inserting rows,deleting rows.  i put the macro in the debug
> >mode and made the excel sheet visible and i went thru each step of the
> >code..it goes without any problem , but the rows/cells doesn't get
> selected.
> >when i made the excel visisble, the workbook is opened in readonly
> mode..but
> >during the execution of the code, when there is a "cells(2,3)="data", it
> >gets inserted, but when i try to select this cell and make it bold it
> >doesn't work.
> >can anybody help me in this.

> >i tried the same from outside this application(Microstrategy DSS
> >Broadcaster). the macro (beforesave event) runs perfectly fine selecting
> >cells,making it bold, inserting rows etc.

> >Thanks,
> >Annie Wu



Sat, 08 Jun 2002 03:00:00 GMT  
 how to select columns using macro/vbscript from a readonly excel file
Tom,
we tried changing the code..removed the select statements...but didn't
work.we are exploring other options..
Thanks,
Cini

Quote:
> I guess the lesson would be that you can't select anything unless it is
> visible and in most cases you don't need to.

> Change any code like

> Cells(3,5).Select
> Selection.EntireRow.Insert

> to

> Cells(3,5).EntireRow.Insert

> Remove all the
> Select - selection pairs.

> Why post to:
> microsoft.public.word.vba.beginners,
> microsoft.public.word.vba.customization,
> microsoft.public.word.vba.general

> if this is an excel problem.  Hopefully you will get back to the groups I
> limited my answer to.

> HTH,
> Tom Ogilvy
> MVP Excel


> >i have an application(microstrategy DSS Broadcaster) which exports data
to
> >excel sheet. the appication does the export making excel invisible. once
> the
> >export is over , i am using workbook_beforesave event to execute a macro
> >which formats the data in the excel sheet. what i want is to insert some
> >rows into the sheet, add some data into the colums, delete some columns
and
> >make some data bold(to do totaling and sub totaling). when i execute
this,
> >all the code is getting executed except the selecting and making it bold,
> >selecting and inserting rows,deleting rows.  i put the macro in the debug
> >mode and made the excel sheet visible and i went thru each step of the
> >code..it goes without any problem , but the rows/cells doesn't get
> selected.
> >when i made the excel visisble, the workbook is opened in readonly
> mode..but
> >during the execution of the code, when there is a "cells(2,3)="data", it
> >gets inserted, but when i try to select this cell and make it bold it
> >doesn't work.
> >can anybody help me in this.

> >i tried the same from outside this application(Microstrategy DSS
> >Broadcaster). the macro (beforesave event) runs perfectly fine selecting
> >cells,making it bold, inserting rows etc.

> >Thanks,
> >Annie Wu



Sat, 08 Jun 2002 03:00:00 GMT  
 how to select columns using macro/vbscript from a readonly excel file
The symptoms on inserting rows and not changing the font sound like the
worksheet might be protected. Have you check to see if there is any
protection applied?
I opened a workbook as read only and ran this macro with no problem:

Sub tester1()
For Each Cell In Range("A1:A10")
  Cell.Font.Name = "Times New Roman"
  Cell.Font.Bold = True
  Cell.Value = Rnd()
Next
Cells(5, "A").EntireRow.Insert
End Sub

When I protected the sheet, even when the cells were unlocked, I could not
set the font to bold and I could not Insert a row.

Regards,
Tom Ogilvy
MVP Excel


Quote:
> Tom,

> I am working with annie in this case. we made the excel sheet visible
while
> running the macro.when we made it visible it opened as read only.  we were
> able to insert data into cells, but not able to change the font of the
cell
> or size etc or to insert rows into it. we also tried saving this workbook
as
> a different workbook. it doesn't save this as another workbook also.
> Thanks,
> Cini Sathyavan



> > I guess the lesson would be that you can't select anything unless it is
> > visible and in most cases you don't need to.

> > Change any code like

> > Cells(3,5).Select
> > Selection.EntireRow.Insert

> > to

> > Cells(3,5).EntireRow.Insert

> > Remove all the
> > Select - selection pairs.

> > Why post to:
> > microsoft.public.word.vba.beginners,
> > microsoft.public.word.vba.customization,
> > microsoft.public.word.vba.general

> > if this is an excel problem.  Hopefully you will get back to the groups
I
> > limited my answer to.

> > HTH,
> > Tom Ogilvy
> > MVP Excel


> > >i have an application(microstrategy DSS Broadcaster) which exports data
> to
> > >excel sheet. the appication does the export making excel invisible.
once
> > the
> > >export is over , i am using workbook_beforesave event to execute a
macro
> > >which formats the data in the excel sheet. what i want is to insert
some
> > >rows into the sheet, add some data into the colums, delete some columns
> and
> > >make some data bold(to do totaling and sub totaling). when i execute
> this,
> > >all the code is getting executed except the selecting and making it
bold,
> > >selecting and inserting rows,deleting rows.  i put the macro in the
debug
> > >mode and made the excel sheet visible and i went thru each step of the
> > >code..it goes without any problem , but the rows/cells doesn't get
> > selected.
> > >when i made the excel visisble, the workbook is opened in readonly
> > mode..but
> > >during the execution of the code, when there is a "cells(2,3)="data",
it
> > >gets inserted, but when i try to select this cell and make it bold it
> > >doesn't work.
> > >can anybody help me in this.

> > >i tried the same from outside this application(Microstrategy DSS
> > >Broadcaster). the macro (beforesave event) runs perfectly fine
selecting
> > >cells,making it bold, inserting rows etc.

> > >Thanks,
> > >Annie Wu



Sat, 08 Jun 2002 03:00:00 GMT  
 how to select columns using macro/vbscript from a readonly excel file
Why don't copy all the sheets into a new file? Or only the data? Kill the old
file and save the new one named as the old one...

claus

Quote:

> Tom,

> I am working with annie in this case. we made the excel sheet visible while
> running the macro.when we made it visible it opened as read only.  we were
> able to insert data into cells, but not able to change the font of the cell
> or size etc or to insert rows into it. we also tried saving this workbook as
> a different workbook. it doesn't save this as another workbook also.
> Thanks,
> Cini Sathyavan



> > I guess the lesson would be that you can't select anything unless it is
> > visible and in most cases you don't need to.

> > Change any code like

> > Cells(3,5).Select
> > Selection.EntireRow.Insert

> > to

> > Cells(3,5).EntireRow.Insert

> > Remove all the
> > Select - selection pairs.

> > Why post to:
> > microsoft.public.word.vba.beginners,
> > microsoft.public.word.vba.customization,
> > microsoft.public.word.vba.general

> > if this is an excel problem.  Hopefully you will get back to the groups I
> > limited my answer to.

> > HTH,
> > Tom Ogilvy
> > MVP Excel


> > >i have an application(microstrategy DSS Broadcaster) which exports data
> to
> > >excel sheet. the appication does the export making excel invisible. once
> > the
> > >export is over , i am using workbook_beforesave event to execute a macro
> > >which formats the data in the excel sheet. what i want is to insert some
> > >rows into the sheet, add some data into the colums, delete some columns
> and
> > >make some data bold(to do totaling and sub totaling). when i execute
> this,
> > >all the code is getting executed except the selecting and making it bold,
> > >selecting and inserting rows,deleting rows.  i put the macro in the debug
> > >mode and made the excel sheet visible and i went thru each step of the
> > >code..it goes without any problem , but the rows/cells doesn't get
> > selected.
> > >when i made the excel visisble, the workbook is opened in readonly
> > mode..but
> > >during the execution of the code, when there is a "cells(2,3)="data", it
> > >gets inserted, but when i try to select this cell and make it bold it
> > >doesn't work.
> > >can anybody help me in this.

> > >i tried the same from outside this application(Microstrategy DSS
> > >Broadcaster). the macro (beforesave event) runs perfectly fine selecting
> > >cells,making it bold, inserting rows etc.

> > >Thanks,
> > >Annie Wu

--
Claus Ohlendorf
RKD Gesellschaft fuer Softwareentwicklung mbH
Postfach 801527
81615 Muenchen
Germany

Tel: 0049 89 4152 220
Fax: 0049 89 4152 380




Sun, 23 Jun 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Run macro in Excel using vbscript

2. Importing text file into Excel using VBA - from a word macro

3. VB / Excel file open from FTP server - Readonly

4. Problem using datatable.select using multiple columns

5. Running an excel macro using excel 8 obj lib

6. Running an excel macro using excel 8 obj lib

7. Exporting Outlook Contacts to an Excel spreadsheet or to a txt file by using VBScript

8. Select variable length columns in macro

9. How to use selected columns in a macro

10. Reading Excel file using VBScript

11. create an empty excel file using vbscript

12. Opening an excel file through a form/running excel macros through access

 

 
Powered by phpBB® Forum Software