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
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