Pivot and VB scripting range problem
 

Hello,

If this is not the correct group, please let me know which one is.

I am pretty new to Excel scripting. I am able to do what I need most of
the time.

My problem is that I want to place a pivot table in a scriptand that
works, but the
data range is never the same. I have recorded a macro an that works
for said data. It gives me the following:


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _
).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1"

However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be
different almost each time.

I have tried to take a range that is way too big, but then the Pivot
table gives me the wrong results.
So what is the best way to select the correct range each time?

I have searched with Google for a few days, but came up with nothing.
:-(

I am working with Excel 2000. Sorry, no option in updating or upgrading.
Company policy.

houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Pivot and VB scripting range problem
 

Hi

you could insert some code to find the lastrow

Dim lastrow as long
Lastrow = Cells(Rows.Count, 1).End(xlUp).row

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"

--
Regards

Roger Govier


"houghi" wrote in message
...
Hello,

If this is not the correct group, please let me know which one is.

I am pretty new to Excel scripting. I am able to do what I need most
of
the time.

My problem is that I want to place a pivot table in a scriptand that
works, but the
data range is never the same. I have recorded a macro an that works
for said data. It gives me the following:


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"

However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be
different almost each time.

I have tried to take a range that is way too big, but then the Pivot
table gives me the wrong results.
So what is the best way to select the correct range each time?

I have searched with Google for a few days, but came up with nothing.
:-(

I am working with Excel 2000. Sorry, no option in updating or
upgrading.
Company policy.

houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Pivot and VB scripting range problem
 

I get a 'Runtime error 1004'. Reference is not valid. With 'debug' the
whole line is yellow. When I change !Range(Cells(2,1),cells(lastrow,5))
back to !R2C1:R37C5 then it works again.

Could it be that this not work because I work with Excel 2000? If that
is the case, is there a different solution?

Roger Govier wrote:
Hi

you could insert some code to find the lastrow

Dim lastrow as long
Lastrow = Cells(Rows.Count, 1).End(xlUp).row

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"



houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Pivot and VB scripting range problem
 

Hi

What value shows for lastrow?

--
Regards

Roger Govier


"houghi" wrote in message
...

I get a 'Runtime error 1004'. Reference is not valid. With 'debug' the
whole line is yellow. When I change
!Range(Cells(2,1),cells(lastrow,5))
back to !R2C1:R37C5 then it works again.

Could it be that this not work because I work with Excel 2000? If that
is the case, is there a different solution?

Roger Govier wrote:
Hi

you could insert some code to find the lastrow

Dim lastrow as long
Lastrow = Cells(Rows.Count, 1).End(xlUp).row

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5))
_
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"



houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Pivot and VB scripting range problem
 

I have added the following
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lastrow

That shows 37. To be sure I even did ` lastrow = "37" ` so there won't
be an issue with calculation lastrow.

Roger Govier wrote:
Hi

What value shows for lastrow?



houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Pivot and VB scripting range problem
 

Can you open that report.xls file to find the last used row?

dim RptWkbk as workbook
dim ActWks as worksheet
dim LastRow as long
dim PTRng as range

set actwkbk = activesheet

set rptwkbk = workbooks.open(Filename:="y:\Report.xls")
with rptwkbk.worksheets(1)
'I used column A to find the last used row
lastrow = .cells(.rows.count,"A").end(xlup).row
set ptrng = .range("a2:E" & lastrow)
end with

'do more stuff???
with actwks
.parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ptrng.address(external:=true)) _
.CreatePivotTable TableDestination:=.Range("A4"), _
TableName:="PivotTable1"

'do more stuff
rptwkbk.close savechanges:=false

====
Uncompiled, untested. Watch for typos.


houghi wrote:

Hello,

If this is not the correct group, please let me know which one is.

I am pretty new to Excel scripting. I am able to do what I need most of
the time.

My problem is that I want to place a pivot table in a scriptand that
works, but the
data range is never the same. I have recorded a macro an that works
for said data. It gives me the following:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _
).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1"

However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be
different almost each time.

I have tried to take a range that is way too big, but then the Pivot
table gives me the wrong results.
So what is the best way to select the correct range each time?

I have searched with Google for a few days, but came up with nothing.
:-(

I am working with Excel 2000. Sorry, no option in updating or upgrading.
Company policy.

houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

--

Dave Peterson

Pivot and VB scripting range problem
 

Thanks, I get the basic idea, but unfortunatly I am still in the copy
and paste fase of learning the language, so the compile error I get is a
stopper for me. I do not know how to solve that.
I have added sub Pivot_2() at the beginning and End Sub at the end.

The rest will be for monday, as I have no access to Excel over the
weekend.

Dave Peterson wrote:
Can you open that report.xls file to find the last used row?

dim RptWkbk as workbook
dim ActWks as worksheet
dim LastRow as long
dim PTRng as range

set actwkbk = activesheet

set rptwkbk = workbooks.open(Filename:="y:\Report.xls")
with rptwkbk.worksheets(1)
'I used column A to find the last used row
lastrow = .cells(.rows.count,"A").end(xlup).row
set ptrng = .range("a2:E" & lastrow)
end with

'do more stuff???
with actwks
.parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ptrng.address(external:=true)) _
.CreatePivotTable TableDestination:=.Range("A4"), _
TableName:="PivotTable1"

'do more stuff
rptwkbk.close savechanges:=false

====
Uncompiled, untested. Watch for typos.


houghi wrote:

Hello,

If this is not the correct group, please let me know which one is.

I am pretty new to Excel scripting. I am able to do what I need most of
the time.

My problem is that I want to place a pivot table in a scriptand that
works, but the
data range is never the same. I have recorded a macro an that works
for said data. It gives me the following:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _
).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1"

However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be
different almost each time.

I have tried to take a range that is way too big, but then the Pivot
table gives me the wrong results.
So what is the best way to select the correct range each time?

I have searched with Google for a few days, but came up with nothing.
:-(

I am working with Excel 2000. Sorry, no option in updating or upgrading.
Company policy.

houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.



houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Pivot and VB scripting range problem
 

I had a couple of typos (sorry).

This compiles, but I didn't test it:

Option Explicit
Sub testme()

Dim RptWkbk As Workbook
Dim ActWks As Worksheet
Dim LastRow As Long
Dim PTRng As Range

Set ActWks = ActiveSheet '<--changed

Set RptWkbk = Workbooks.Open(Filename:="y:\Report.xls")
With RptWkbk.Worksheets(1)
'I used column A to find the last used row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set PTRng = .Range("a2:E" & LastRow)
End With

'do more stuff???
With ActWks
.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PTRng.Address(external:=True)) _
.CreatePivotTable TableDestination:=.Range("A4"), _
TableName:="PivotTable1"
End With '<--added

'do more stuff
RptWkbk.Close savechanges:=False

End Sub



houghi wrote:

Thanks, I get the basic idea, but unfortunatly I am still in the copy
and paste fase of learning the language, so the compile error I get is a
stopper for me. I do not know how to solve that.
I have added sub Pivot_2() at the beginning and End Sub at the end.

The rest will be for monday, as I have no access to Excel over the
weekend.

Dave Peterson wrote:
Can you open that report.xls file to find the last used row?

dim RptWkbk as workbook
dim ActWks as worksheet
dim LastRow as long
dim PTRng as range

set actwkbk = activesheet

set rptwkbk = workbooks.open(Filename:="y:\Report.xls")
with rptwkbk.worksheets(1)
'I used column A to find the last used row
lastrow = .cells(.rows.count,"A").end(xlup).row
set ptrng = .range("a2:E" & lastrow)
end with

'do more stuff???
with actwks
.parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ptrng.address(external:=true)) _
.CreatePivotTable TableDestination:=.Range("A4"), _
TableName:="PivotTable1"

'do more stuff
rptwkbk.close savechanges:=false

====
Uncompiled, untested. Watch for typos.


houghi wrote:

Hello,

If this is not the correct group, please let me know which one is.

I am pretty new to Excel scripting. I am able to do what I need most of
the time.

My problem is that I want to place a pivot table in a scriptand that
works, but the
data range is never the same. I have recorded a macro an that works
for said data. It gives me the following:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _
).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1"

However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be
different almost each time.

I have tried to take a range that is way too big, but then the Pivot
table gives me the wrong results.
So what is the best way to select the correct range each time?

I have searched with Google for a few days, but came up with nothing.
:-(

I am working with Excel 2000. Sorry, no option in updating or upgrading.
Company policy.

houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.


houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

--

Dave Peterson

Pivot and VB scripting range problem
 

Dave Peterson wrote:
I had a couple of typos (sorry).

This compiles, but I didn't test it:

Option Explicit
<snip

This works. Great, thanks.

houghi
--
Let's not be too tough on our own ignorance. It's the thing that makes
America great. If America weren't incomparably ignorant, how could we
have tolerated the last eight years? -- Frank Zappa, in 1988