Results 1 to 4 of 4

Thread: VBA Question

  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: VBA Question

    I have a code that creates an Excel file and then it populates it with a query i have written in VBA. I have used this before and it worked. For some reason i get an error when i run this:

    Dim Division(3) As Integer
    Dim i As Integer

    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset

    Dim TotalsRow As Integer

    Dim strSQLNew As String




    Set db = CurrentDb

    Division(1) = 001
    Division(2) = 002
    Division(3) = 003

    For i = 1 To 3


    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    objXL.Application.workbooks.Add
    objXL.Application.Sheets.Add Count:=3
    Set objActiveWkb = objXL.Application.ActiveWorkBook


    strSQLNew = "SELECT tblTotals.[Type], tblTotals.[SALES-ROUTE], tblTotals.[CountOf1:30], " _
    & "tblTotals.[SumOf1:30], tblTotals.[CountOf31:60], tblTotals.[SumOf31:60], tblTotals.[CountOf61:90], " _
    & "tblTotals.[SumOf61:90], tblTotals.[CountOf91:120], tblTotals.[SumOf91:120], tblTotals.[CountOf121:150], tblTotals.[SumOf121:150], " _
    & "tblTotals.[CountOf151:180], tblTotals.[SumOf151:180], tblTotals.[CountOf181:], tblTotals.[SumOf181:] " _
    & "FROM tblTotals WHERE ([tblTotals].[Division])=" & Division(i) & ";"



    Set rs = db.OpenRecordset(strSQLNew, 2)


    With objActiveWkb

    .Worksheets(1).Cells(5, 1) = "Type"
    .Worksheets(1).Cells(5, 2) = "Sales Route"
    .Worksheets(1).Cells(5, 3) = "1 - 30 #"
    .Worksheets(1).Cells(5, 4) = "1 - 30 $"
    .Worksheets(1).Cells(5, 5) = "31 - 60 #"
    .Worksheets(1).Cells(5, 6) = "31 - 60 $"
    .Worksheets(1).Cells(5, 7) = "61 - 90 #"
    .Worksheets(1).Cells(5, 8) = "61 - 90 $"
    .Worksheets(1).Cells(5, 9) = "91 - 120 #"
    .Worksheets(1).Cells(5, 10) = "91 - 120 $"
    .Worksheets(1).Cells(5, 11) = "121 - 150 #"
    .Worksheets(1).Cells(5, 12) = "121 - 150 $"
    .Worksheets(1).Cells(5, 13) = "151 - 180 #"
    .Worksheets(1).Cells(5, 14) = "151 - 180 $"
    .Worksheets(1).Cells(5, 15) = "181+ #"
    .Worksheets(1).Cells(5, 16) = "180+ $"

    TotalsRow = 6
    While rs.EOF <> True

    .Worksheets(1).Cells(TotalsRow, 1) = rs![Type]
    .Worksheets(1).Cells(TotalsRow, 2) = rs![SALES-ROUTE]
    .Worksheets(1).Cells(TotalsRow, 3) = rs![CountOf1:30]
    .Worksheets(1).Cells(TotalsRow, 4) = rs![SumOf1:30]
    .Worksheets(1).Cells(TotalsRow, 5) = rs![Count0f31:60]
    .Worksheets(1).Cells(TotalsRow, 6) = rs![SumOf31:60]
    .Worksheets(1).Cells(TotalsRow, 7) = rs![Count0f61:90]
    .Worksheets(1).Cells(TotalsRow, 8) = rs![SumOf61:90]
    .Worksheets(1).Cells(TotalsRow, 9) = rs![Count0f91:120]
    .Worksheets(1).Cells(TotalsRow, 10) = rs![SumOf91:120]
    .Worksheets(1).Cells(TotalsRow, 11) = rs![Count0f121:150]
    .Worksheets(1).Cells(TotalsRow, 12) = rs![SumOf121:150]
    .Worksheets(1).Cells(TotalsRow, 13) = rs![Count0f151:180]
    .Worksheets(1).Cells(TotalsRow, 14) = rs![SumOf151:180]
    .Worksheets(1).Cells(TotalsRow, 15) = rs![Count0f181]
    .Worksheets(1).Cells(TotalsRow, 16) = rs![SumOf181]

    rs.MoveNext
    TotalsRow = TotalsRow + 1
    Wend

    End With

    objActiveWkb.SaveAs Filename:="F:\" & Division(i) & "AR STATUS.xls"
    objActiveWkb.Close savechanges:=True

    Set objActiveWkb = Nothing
    Set objXL = Nothing

    Next i

    I have BOLDED the line where I get the error. Its like the code stops reading the query/table. I reviewed the query and it when i run it in a normal query but not here.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and what do you see if you put
    msgbox "This is the funny line: " & rs![CountOf1:30]
    just before the error line?

    ?? does access accept the ":" in a recordset field name ?? --- i don't know, i have never used such notation.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    according to help, it accepts ":"

    Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]).


    Can't begin with leading spaces.


    Can't include control characters (ASCII values 0 through 31).


    Can't include a double quotation mark (") in table, view, or stored procedure names in a Microsoft Access project.


    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Your right IZY

    Access didn't like the : in the field name. When i changed it- the query worked perfectly. THANKS!!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •