assigning variable to worksheets

[armesca]

Can anyone help me with my cases? I keep getting subscript out of range errors when I try to set my excel worksheets. Port is the name of the tab in excel and stPort is the name in my query. ANy help is appreciated:

Function SendToPort()
Dim rsOut As DAO.Recordset
Dim objExcel As Object
Dim objWB As Object
Dim objWS As Object
Dim strSQL As String
Dim i As Integer
Dim stPort As String
Dim Port As String

i = 1

Do While i <= 10
Select Case i
Case 1
stPort = "Albany"
Port = "Albany"
Case 2
stPort = "Anchorage"
Port = "Anchorage"
Case 3
stPort = "Baltimore"
Port = "Baltimore"
Case 4
stPort = "Boston"
Port = "Boston"
Case 5
stPort = "Buffalo"
Port = "Buffalo"
Case 6
stPort = "Charleston SC"
Port = "Charleston SC"
Case 7
stPort = "Cincinnati"
Port = "Cincinnati"
Case 8
stPort = "Cleveland"
Port = "Cleveland"
Case 9
stPort = "Columbia-Snake River System"
Port = "Columbia-Snake River System"
Case 10
stPort = "Corpus Christi"
Port = "Corpus Christi"
End Select

Set objExcel = CreateObject("Excel.Application")

Set objWB = objExcel.Workbooks.Open _
("C:\Documents and Settings\carmes\Desktop\PFSR\USCG-PSGP & ad-hoc TSGP\USCG_PFSR_03_31_2011.xlsx")

Set objWS = objWB.Worksheets("& Port")

strSQL = "Select [Grantee Year], [Award Number], [FA/Entity], [Current Obligated Amount], [Amount Released], [Amount on Hold], [Draw Downs], [Percent of Released Funds Drawn Down], [Balance], [Hold Reason]From [PSGP2 - Summary Final] WHERE [Port Area] = '" & stPort & "'"


Set rsOut = Application.CurrentDb.OpenRecordset(strSQL)

objWS.Range("B3").CopyFromRecordset rsOut

rsOut.Close

objWB.Save

objWB.Close

Set objWS = Nothing
Set objWB = Nothing

i = i + 1

objExcel.Quit
Set objExcel = Nothing
Loop

Set objWS = Nothing
Set objWB = Nothing

MsgBox "Port Export Complete"


End Function

 

[boblarson]

It would be

Set objWS = objWB.Worksheets(Port)


I do have a question as to why you have two of the same thing:

stPort

and

Port


What is the purpose behind having both of those in each of the Case Statements???

 

[boblarson]

Oh, and you shouldn't be creating an Excel object for each iteration and then quitting and setting to nothing. Just do it ONCE for the whole set and then do that cleanup at the end.