Prev: button name scope and Caption
Next: Selecting Multiple Controls
From: stan on 3 Apr 2010 12:38
I have a small marco that loads data from a file from another application
into a workbook in Excel 2003. Sometimes the number of rows exceeds the
65,000+ cap so the remaining data spills over to a second sheet, sometimes
not.

I need a line or two that looks for sheet2: if it finds id it copies all the
data from sheets 1 & 2. If there is no sheet 2, the procedure continues and
it loads sheet 1.

Nothing I try seems to work. I am a beginner with VBA.
--
stan
From: Project Mangler on 3 Apr 2010 13:00
Stan,

Have a look here:
http://www.cpearson.com/excel/ImportBigFiles.aspx

DB

"stan" <stan(a)discussions.microsoft.com> wrote in message
news:6E84E685-22B0-4D48-A2EE-C3FFC3323BE7(a)microsoft.com...
> I have a small marco that loads data from a file from another application
> into a workbook in Excel 2003. Sometimes the number of rows exceeds the
> 65,000+ cap so the remaining data spills over to a second sheet, sometimes
> not.
>
> I need a line or two that looks for sheet2: if it finds id it copies all
the
> data from sheets 1 & 2. If there is no sheet 2, the procedure continues
and
> it loads sheet 1.
>
> Nothing I try seems to work. I am a beginner with VBA.
> --
> stan


From: Barb Reinhardt on 3 Apr 2010 13:27
Dim myWS as excel.worksheet

on error resume next
Set myWS = ActiveBook.Worksheets("Sheet2")
on error goto 0

if myws is nothing then
MSgbox("There is no Sheet2")
end if


'Alternatively, you can try this if there are only two worksheets in the
workbook.

if ActiveBook.WOrksheets.count = 2 then
Msgbox("There are two worksheets")
elseif activebook.worksheets.count = 1 then
Msgbox("There is one worksheet")

end if

HTH,

Barb Reinhardt



"stan" wrote:

> I have a small marco that loads data from a file from another application
> into a workbook in Excel 2003. Sometimes the number of rows exceeds the
> 65,000+ cap so the remaining data spills over to a second sheet, sometimes
> not.
>
> I need a line or two that looks for sheet2: if it finds id it copies all the
> data from sheets 1 & 2. If there is no sheet 2, the procedure continues and
> it loads sheet 1.
>
> Nothing I try seems to work. I am a beginner with VBA.
> --
> stan
From: stan on 5 Apr 2010 11:18
Works prefectly. Thank you.
--
stan


"Barb Reinhardt" wrote:

> Dim myWS as excel.worksheet
>
> on error resume next
> Set myWS = ActiveBook.Worksheets("Sheet2")
> on error goto 0
>
> if myws is nothing then
> MSgbox("There is no Sheet2")
> end if
>
>
> 'Alternatively, you can try this if there are only two worksheets in the
> workbook.
>
> if ActiveBook.WOrksheets.count = 2 then
> Msgbox("There are two worksheets")
> elseif activebook.worksheets.count = 1 then
> Msgbox("There is one worksheet")
>
> end if
>
> HTH,
>
> Barb Reinhardt
>
>
>
> "stan" wrote:
>
> > I have a small marco that loads data from a file from another application
> > into a workbook in Excel 2003. Sometimes the number of rows exceeds the
> > 65,000+ cap so the remaining data spills over to a second sheet, sometimes
> > not.
> >
> > I need a line or two that looks for sheet2: if it finds id it copies all the
> > data from sheets 1 & 2. If there is no sheet 2, the procedure continues and
> > it loads sheet 1.
> >
> > Nothing I try seems to work. I am a beginner with VBA.
> > --
> > stan
 | 
Pages: 1
Prev: button name scope and Caption
Next: Selecting Multiple Controls