Access VBA to excel (1 Viewer)

[armesca]

I have one template consisting of one tab in excel. I have a loop to cycle through criteria in a query and paste it out to excel. I am having a problem with my y select statement(in bold below). It is not actually pasting the text in the denoted cell. Any help is appreciated. Other issues you can point out are appreciated as well. It could be a loop placement issue. Thanks:

Private Sub cmd_topten_bal_Click()
Dim rs As DAO.Recordset
Dim objwb As Object
Dim objexcel As Object
Dim objws As Object
Dim i As Integer
Dim Program As String
Dim v As Integer
Dim mystring As String
Dim sqlstring As String
Dim mycrit As String
Dim mytitle As String
Dim myyear As String
Dim mylocation As String
Dim y As Integer


i = 1
v = 1
Set objexcel = CreateObject("excel.application")
Set objwb = objexcel.Workbooks.Open _
("C:\Documents and Settings\carmes\Desktop\PFSR Templates\Top Ten Template.xlsx")
Do While i <= 3
y = 1
Select Case i
Case 1
Program = "TSGP"
mycrit = "Transit*'"
mytitle = "Transit Security Grant Program"
Case 2
Program = "PSGP"
mycrit = "Port*'"
mytitle = "Port Security Grant Program"
Case 3
Program = "HSGP"
mycrit = "Homeland*' AND [Fiscal Year/Program] NOT LIKE '*Tribal*'"
mytitle = "Homeland Security Grant Program"

End Select

objwb.Sheets("Template").Copy after:=objwb.Sheets(v)
v = v + 1
Set objws = objwb.Sheets(v)
Do While y <= 3

Select Case y
Case 1
myyear = "2006"
mylocation = "objws.Range('A1').Value"
Case 2
myyear = "2007"
mylocation = "objws.Range('A42').Value"
Case 3
myyear = "2008"
mylocation = "objws.Range('A82').Value"
End Select
y = y + 1

mylocation = myyear & " " & mytitle
MsgBox mylocation
Loop

objexcel.Range("A2").Value = "As of " & Forms.USCGForm1.txt_cfodate2.Value
objwb.Sheets(v).Name = "Top Ten " & Program
'MsgBox objwb.Sheets(v).Name

sqlstring = "Select TOP 10 [Obligation Number],[Vendor name],[Vendor state code],[Current obligated amount],[draw downs],[Award balance] FROM [All details table] WHERE [Fiscal Year/Program] Like '*2006" & " " & mycrit & " ORDER BY [Award Balance] DESC;"
'MsgBox sqlstring
'MsgBox sqlstring
Set rs = Application.CurrentDb.OpenRecordset(sqlstring)
objws.Range("A5").CopyFromRecordset rs
rs.Close
i = i + 1
Loop
Set objws = objwb.Worksheets("Template")
Set objws = Nothing
mystring = "C:\Documents and Settings\carmes\Desktop\PFSR Final\Trends_Analysis_Top_Ten " & Forms!USCGForm1!txt_cfodate2.Value
'MsgBox mystring
mystring = Replace(mystring, "/", "")
objwb.SaveAs (mystring), FileFormat:=51
objwb.Close
SetAttr mystring & ".xlsx", vbReadOnly
Set objwb = Nothing
objexcel.Quit
Set objexcel = Nothing
MsgBox "Export Complete"

 

[DirMex]

Hi

Maybe not the most ideal answer, because essentially I couldn't be arsed reading through the code trying to figure out where it's going wrong, but I do have a tip that might prove useful.

Instead of create the new excel object via that method, if you add the 'Microsoft Excel 12.0 Object Library' in the references for VBA developer, you might find writing the code for excel much easier as it gives you all the properties/action lists for excel objects.
So once you add the reference, create your excel like this

Sub ExcelInAccess()

'Go to Tools->References and add the Microsoft Excel 12.0 Object Library

Dim value As Variant
Dim wb As Workbook
Dim xl As Excel.Application
Set xl = New Excel.Application
Set wb = xl.Workbooks.Open("C:\Documents and Settings\carmes\Desktop\PFSR Templates\Top Ten Template.xlsx")
wb.Sheets("Template").Activate

value = Range("A1").value

wb.Close True
Set xl = Nothing

End Sub

 

[armesca]

Thanks, I will try that. I guess more to the point, I would like to know how to put a range into a string, it is not pasting the text into excel in my y case statement:

Do While y <= 3

Select Case y
Case 1
myyear = "2006"
mylocation = "objws.Range('A1').Value"
Case 2
myyear = "2007"
mylocation = "objws.Range('A42').Value"
Case 3
myyear = "2008"
mylocation = "objws.Range('A82').Value"
End Select
y = y + 1

mylocation = myyear & " " & mytitle
'MsgBox mylocation
Loop



Thanks