Trying to send data to nonadjacent worksheets

[PRGMRgirl]

Hello,
I'm sending data from Access 97 to an Excel 97 spreadsheet. I hav
broken up the code in a logical order according to the kind o
formatting needed for each group of sheets.

The problem is when I try to send data to a group of nonadjacen
worksheets. For example, I have data that needs to go to 3 worksheets:
sheet13, sheet16, and sheet26. I have a loop that pulls th
information and puts it on the sheet. I want to cycle through the
worksheets, adding info to each one in turn.

I figured I needed an array, something to identify the three worksheet
as a unit so I can cycle through them. However, I can't seem to ge
the darn thing to work! I have other arrays that all work and this on
is set up the same way so I'm really stumped. I'm not getting an
errors, it just won't assign the value to the variable....

Any help would be much appreciated!

prgmr gir

 

[JE McGimpsey]

Perhaps something like this:

Dim wkSht As Worksheet
For Each wkSht In Worksheets(Array("Sheet13","Sheet16","Sheet26"))
'Do stuff
Next wkSht

 

[David Byrne]

Hi PRGMRgirl,


Would it help to include in your code something like the following........?

For i = 1 to 3

If i = 1 then SheetNm ="sheet13"

If i = 2 then SheetNm ="sheet16"

If i = 3 Then SheetNm ="sheet26"


(Your Process)


End if


David

 

[PRGMRgirl]

Hi guys,

Thanks for your response. I have tried both suggestions and now a
least I am getting an error message. "Object Variable or With bloc
variable not set". I checked all my variables and I can't see anythin
wrong. Here's what it looks like:

Dim wSheet As Worksheet, wbk As Workbook
Dim strSQL(3) As Variant, dbs As Database, rst As Recordset
Dim curRow As Integer, curCol As Integer, i As Integer
Dim w As Integer

Set dbs = CurrentDb

strSQL(0) = "SELECT * FROM PRODLIST_STATE" 'sheet 13
strSQL(1) = "SELECT * FROM PRODLIST_REC" 'sheet 16
strSQL(2) = "SELECT * FROM PRODLIST_TFT" 'sheet 26

Set wbk = ActiveWorkbook

For i = 0 To 2 ' This loops the queries above

For w = 1 To 3 'This is the loop for the worksheets
If w = 1 Then wSheet = Worksheets(13) Else
If w = 2 Then wSheet = wbk.Sheets(16) Else
If w = 3 Then wSheet = wbk.Sheets(26)

I tried to Set wSheet = wbk.ActiveSheet but I still get the same error
I also tried:

For Each wSheet In Worksheets(Array("Sheet13","Sheet16","Sheet26"))

Next wSheet

I get the same error....I'm totally stumped. Hopefully, you guys ca
see some error in the above that I can't. I've been staring at thi
thing for TOO long I'm certain I'm just missing something obvious.
This thing is driving me to drink

Thanks!

prgmr girl (@$%#!

 

[PRGMRgirl]

Hey guys,

Thank you! I got the darn thing to work! Now I can sleep at night


You guys rock! Let's do virtual lunch!

prgmr gir