Excel Sheets (running from Access)
 

windows 2000
excel and access 2002
Hi,
Looking for help.
Running Excel from Access.
Have created several Worksheets in an excel spreadsheet Labeling all
starting with R-
I did this from Excel and it put the sheets in R-1, R-2 etc with R-1 on the
far left and R-22 on the far right.
When I do it from Access I get it reversed.
When I did the following macro from excel I got R-1 first and R-22 last on
my combined sheet.
Now from Access I get R-22 first and R-1 last.
Is there a way in the following macro to tell it to start with the first
sheet?
I have tried: with no effect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets(1).Activate

Would sure appreciate any help.
Thanks.....

Public Sub Simulation_All()
'To combine all the Simulation Calculation dBA into one Worksheet
Dim strSheetName As String
Dim strRange As String
Dim i As Integer

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A2").Value = "Combine all sheets R-"
Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"

'Loop through all Sheets for those starting with "R-"
i = 4
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Worksheets(1).Activate
Set FirstBook = Workbooks.Item(1)
' Sheets(1).Select
For Each shtNext In Sheets
strSheetName = shtNext.Name
'for only the sheets starting with R-
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("K3:K6").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub

Excel Sheets (running from Access)
 

If I understand this correctly, your problem is that the loop "For Each
shtNext in Sheets" does not necessarily return the sheets in the order
R-1, R-2, etc. This is happening because Sheets is a collection, and
you can not control the order.

If you know there are exactly 22 sheets, then do something like this:

For i = 1 to 22
strName = "R-" + CStr (i)
set shtNext = Sheets(strName)
.....
next i

Of course, this will need to be doctored if there are a variable number
of sheets, or if some names do not occur.

Hope this helps,
Dom




FGM wrote:
windows 2000
excel and access 2002
Hi,
Looking for help.
Running Excel from Access.
Have created several Worksheets in an excel spreadsheet Labeling all
starting with R-
I did this from Excel and it put the sheets in R-1, R-2 etc with R-1 on the
far left and R-22 on the far right.
When I do it from Access I get it reversed.
When I did the following macro from excel I got R-1 first and R-22 last on
my combined sheet.
Now from Access I get R-22 first and R-1 last.
Is there a way in the following macro to tell it to start with the first
sheet?
I have tried: with no effect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets(1).Activate

Would sure appreciate any help.
Thanks.....

Public Sub Simulation_All()
'To combine all the Simulation Calculation dBA into one Worksheet
Dim strSheetName As String
Dim strRange As String
Dim i As Integer

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A2").Value = "Combine all sheets R-"
Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"

'Loop through all Sheets for those starting with "R-"
i = 4
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Worksheets(1).Activate
Set FirstBook = Workbooks.Item(1)
' Sheets(1).Select
For Each shtNext In Sheets
strSheetName = shtNext.Name
'for only the sheets starting with R-
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("K3:K6").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub

Excel Sheets (running from Access)
 

Hi,
thank you for your suggestion. I will look at it. I did find something
that would work for me.....

Sheets.Add After:=ActiveSheet
I knew there was most likely a simple solution... but not always easy to find.
Thank you it is most appreciated.


" wrote:

If I understand this correctly, your problem is that the loop "For Each
shtNext in Sheets" does not necessarily return the sheets in the order
R-1, R-2, etc. This is happening because Sheets is a collection, and
you can not control the order.

If you know there are exactly 22 sheets, then do something like this:

For i = 1 to 22
strName = "R-" + CStr (i)
set shtNext = Sheets(strName)
.....
next i

Of course, this will need to be doctored if there are a variable number
of sheets, or if some names do not occur.

Hope this helps,
Dom




FGM wrote:
windows 2000
excel and access 2002
Hi,
Looking for help.
Running Excel from Access.
Have created several Worksheets in an excel spreadsheet Labeling all
starting with R-
I did this from Excel and it put the sheets in R-1, R-2 etc with R-1 on the
far left and R-22 on the far right.
When I do it from Access I get it reversed.
When I did the following macro from excel I got R-1 first and R-22 last on
my combined sheet.
Now from Access I get R-22 first and R-1 last.
Is there a way in the following macro to tell it to start with the first
sheet?
I have tried: with no effect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets(1).Activate

Would sure appreciate any help.
Thanks.....

Public Sub Simulation_All()
'To combine all the Simulation Calculation dBA into one Worksheet
Dim strSheetName As String
Dim strRange As String
Dim i As Integer

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A2").Value = "Combine all sheets R-"
Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"

'Loop through all Sheets for those starting with "R-"
i = 4
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Worksheets(1).Activate
Set FirstBook = Workbooks.Item(1)
' Sheets(1).Select
For Each shtNext In Sheets
strSheetName = shtNext.Name
'for only the sheets starting with R-
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("K3:K6").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub