Using With in a Loop 1

[Trob70]

Is there any way of having a With Statement use several alias

eg

Dim ww as string

for ll= 1 to 3
WW = "Sheet" & str(ll) 'eg Sheet1 Sheet2 Sheet3

With WW
.Cells(1, 10).Value = ll
End With

next ll

Really appreciate any help

Regards

 

[PeterWallace]

as DIM ww as string reserves a spot in memory to hold a string value

Every time you use ww = "ABC" thats what gets stored there
Overwriting previous entry

You Could use ww = ww & "ABC" to add to memory

Or if you want several WW s then use an ARRAY

DIM ww( 0 to 4 ) as string

For J = 0 to 4
WW(j) = "Sheet" & str(ll) 'eg Sheet1 Sheet2 Sheet3
Next

OR
TYPE WWitem
Sheet as string * 30
Cell as Long
END TYPE

DIM WW(0 to 4) as WWitem


For J = 0 to 4
WW(j).sheet = "Sheet" & str(ll) 'eg Sheet1 Sheet2 Sheet3
WW(j).cell = j
Next

 

[Trob70]

I am sorry i did not completely explain what i am trying to do..!!!


eg.. this is the code

Set Excelapp = CreateObject("Excel.Application")

Set ExcelWorkbook = Excelapp.Workbooks.Add 'Then 1 2 and 3 Sheets

Set ExcelSheet = ExcelWorkbook.Worksheets(1)
ExcelSheet.Name = "All"
Set ExcelSheet2 = ExcelWorkbook.Worksheets(2)
ExcelSheet2.Name = "A"
Set ExcelSheet3 = ExcelWorkbook.Worksheets(3)
ExcelSheet3.Name = "O"

Dim xSheets As Integer

For xSheets = 1 To 3 ' Main Loop.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

rstt.MoveFirst
rstt2.MoveFirst
rstt3.MoveFirst
rstt4.MoveFirst
rstt5.MoveFirst
Colx = 1


With ExcelSheet

'****** this where i have the problem i want to replace ExcelSheet with ExcelSheet if xsheets =1 ExcelSheets2 if xsheets=2 etc ie use an array or something eg with ww(1)

then below have eg

.Cells(1, 10).Value = "Dates This Report"

ie only the .



HoldVenue = ""

Rowx = 3
manyDays = 0

rstt2.MoveFirst
While Not rstt2.EOF

ExcelSheet.Cells(1, 10).Value = "Dates This Report"

Rowx = Rowx + 1
manyDays = manyDays + 1
rstt2.MoveNext
Wend


End With



Appreciate you interest in helping..


Regards

 

[strongm]

>Every time you use ww = "ABC" thats what gets stored there
>Overwriting previous entry

To be pedantic this is eXactly what doesn't happen in VB

 

[strongm]

For xSheets = 1 to 3
' blah
' blah
With ExcelWorkbook.Worksheets(lp)
' blah
' blah
End with
Next

 

[Trob70]

strongm (MIS)

Thanks a lot for your help.

For xSheets = 1 to 3
' blah
' blah
With ExcelWorkbook.Worksheets(lp)
' blah
' blah
End with
Next

if i use xsheets in place of lp i get error 91 object variable or with block variable not set


It works fine on the first loop but comes up with the above where xsheets = 2


??? is this the area i have a problem... should i be creating the sheets in the loop
Set ExcelSheet = ExcelWorkbook.Worksheets(1)
ExcelSheet.Name = "All"
Set ExcelSheet2 = ExcelWorkbook.Worksheets(2)
ExcelSheet2.Name = "A"
Set ExcelSheet3 = ExcelWorkbook.Worksheets(3)
ExcelSheet3.Name = "O"

Really appreciate if you have some time to put me on track

Regards Robert

 

[PeterWallace]

strongm ... I know that it does not get written to memory

Was trying to keep it simple and brief

Pedantic is not my strong point ( although I know that there are times)

 

[strongm]

That's me cobbling the example together too quickly

Firstly:

With ExcelWorkbook.Worksheets(lp)

should indeed be

With ExcelWorkbook.Worksheets(xSheets)

Secondly, the example does rather assume that there are (at least) 3 sheets in existence. And

Excelapp.Workbooks.Add

only adds a workbook with one sheet, not 3 as you seem to have assumed. And thus yes, my code would indeed quite correctly fail exactly as you describe.

 

[Trob70]

strongm (MIS)

Does it mean i have to duplicate all the code 3 times to get a result.


I know in vb.net i have used directcast to get a similiar result.


Really appreciate your offering help., much appreciated.


Regards Robert

 

[strongm]

I am, of coursxe, partially talking nonsense. I was thinking of the number of Workbooks being added, not the number of worksheets in that workbook (it's late here, and my brain isn't what it was ...)

>Does it mean i have to duplicate all the code 3 times to get a result.

No, not at all.

Your code could be something like

[blue]Set Excelapp = CreateObject("Excel.Application")
 
Set ExcelWorkbook = Excelapp.Workbooks.Add 'Then 1 2 and 3 Sheets
 
Set ExcelSheet = ExcelWorkbook.worksheets(1)
ExcelSheet.Name = "All"
Set ExcelSheet2 = ExcelWorkbook.worksheets(2)
ExcelSheet2.Name = "A"
Set ExcelSheet3 = ExcelWorkbook.worksheets(3)
ExcelSheet3.Name = "O"
 
Dim xSheets As Integer
 
For xSheets = 1 To 3 ' Main Loop.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    With ExcelWorkbook.worksheets(xSheets)
        Debug.Print .Name ' this represents where the bulk of your own code would go

    End With
Next[/blue]

Or you could move the naming of the sheets into the loop as well, e.g.

[blue]Set Excelapp = CreateObject("Excel.Application")
Set ExcelWorkbook = Excelapp.Workbooks.Add 'Then 1 2 and 3 Sheets
 
Dim xSheets As Integer
 
For xSheets = 1 To 3 ' Main Loop.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    With ExcelWorkbook.worksheets(xSheets)
        .Name = Array("All", "A", "O")(xSheets - 1) ' Or a Select Case statement if you are feeling more traditional
        Debug.Print .Name ' this represents where the bulk of your own code would go
    End With
Next

End Sub[/blue]

 

[strongm]

>I know that it does not get written to memory

No, no, that bit's right. It is the fact that the old value is not overwritten in memory. VB simply allocates new memory and changes the variable to point there instead (actually it is a little more complex than this under the covers and a search in this forum will find a fairly detailed examination of how VB strings work and how, as a result, strings are generally fairly 'expensive' to use in VB)

But I appreciate that you were, as you say, trying to keep it simple and brief

 

[Trob70]

strongm (MIS)

Thanks very much ... it is all working perfectly now, your time in helping is much appreciated.


Robert