Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Starting to work in w/VBA Need some help

    I looked at pootle_flumps code and trying to make it work for what I'm trying to do. I will admit I am very new to VBA but very excited to learn it.

    I am trying to export a tbl out of access into excel and add some formatting. More specifically filters and summed totals. This is what I have so far and ANY help that you guys can provide is much appreciated.

    Private Sub Command159_Click()

    Dim appexcel As Object
    Set appexcel = CreateObject("Excel.Application")
    appexcel.workbooks.Open "S:\Access\Inventory Reduction\Reports\Manager" & Format(Date, "mm-dd-yyyy") & ".xls"
    appexcel.Visible = True

    appexcel.Sheets("tblManager").Select

    exApp.Interactive = False

    Set exSheet = exBook.Worksheets(1)

    exSheet.Activate

    exSheet.Name = "Manager"

    exSheet.PageSetup.CenterHeader = "Sales by Branch, Salesperson and Product"
    exSheet.PageSetup.RightHeader = "Date Run - " & Format(Date, "Medium date")

    exSheet.PageSetup.CenterFooter = "Sales by Branch, Salesperson and Product"
    exSheet.PageSetup.RightFooter = "Date Run - " & Format(Date, "Medium date")

    If Not rs.EOF Then rs.MoveLast: rs.MoveFirst

    NoOfCols = rs.Fields.Count
    NoOfRows = rs.RecordCount

    exSheet.Range(Chr(64 + LEFTMOST_COL) & HEADER_ROW + 1).CopyFromRecordset rs

    ReDim TotCols(0)

    For i = 0 To NoOfCols - 1

    Set fld = rs.Fields(i)

    exSheet.Cells(HEADER_ROW, i + LEFTMOST_COL).Value = fld.Name


    Next i

    exSheet.Range(exSheet.Cells(HEADER_ROW, LEFTMOST_COL), exSheet.Cells(HEADER_ROW, NoOfCols + LEFTMOST_COL)).AutoFilter

    exApp.ActiveWindow.DisplayGridlines = False

    exSheet.Cells.Range(Chr(64 + LEFTMOST_COL) & HEADER_ROW, ExcelCodes(NoOfCols + LEFTMOST_COL - 1) & HEADER_ROW).Interior.Color = VBA.ColorConstants.vbBlue

    exSheet.Cells.Range(Chr(64 + LEFTMOST_COL) & HEADER_ROW, ExcelCodes(NoOfCols + LEFTMOST_COL - 1) & (NoOfRows + HEADER_ROW)).Borders.Color = RGB(0, 0, 0)

    exSheet.Columns.EntireColumn.AutoFit

    exApp.ActiveWindow.Zoom = 80


    exSheet.PageSetup.Orientation = xlLandscape

    exSheet.Cells(HEADER_ROW + 1, COLUMN_FREEZE + LEFTMOST_COL - 1).Activate
    exApp.ActiveWindow.FreezePanes = True

    exSheet.PageSetup.PrintTitleColumns = "$" & Chr(64 + LEFTMOST_COL) & ":$" & Chr(COLUMN_FREEZE + 63 + LEFTMOST_COL - 1)
    exSheet.PageSetup.PrintTitleRows = "$" & HEADER_ROW & ":$" & HEADER_ROW


    For i = 0 To UBound(TotCols)

    exSheet.Cells(HEADER_ROW + NoOfRows + 1, TotCols(i)).Formula = "=SUM(" & ExcelCodes(TotCols(i)) & HEADER_ROW + 1 & ":" & ExcelCodes(TotCols(i)) & HEADER_ROW + NoOfRows & ")"
    exSheet.Cells(HEADER_ROW + NoOfRows + 1, TotCols(i)).Font.Bold = True
    exSheet.Cells(HEADER_ROW + NoOfRows + 1, TotCols(i)).Font.Size = exSheet.Cells(HEADER_ROW + NoOfRows + 1, TotCols(i)).Font.Size + 2
    exSheet.Cells(HEADER_ROW + NoOfRows + 1, TotCols(i)).Borders.Color = vbBlack

    Next i


    exSheet.Copy , exSheet

    Set exSheet = exBook.Worksheets(2)

    exSheet.Activate

    exSheet.Name = "SubTotals"

    Set exRange = exSheet.Range(exSheet.Cells(HEADER_ROW, LEFTMOST_COL), exSheet.Cells(HEADER_ROW + NoOfRows, NoOfCols + LEFTMOST_COL - 1))

    exRange.Subtotal rs.Fields("Branch").OrdinalPosition + 1, xlSum, TotCols(), , True, xlSummaryBelow



    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What's the question, or the error, or whatever? Offhand, you never declare or set exApp or exBook.
    Paul

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by pbaldy View Post
    What's the question, or the error, or whatever? Offhand, you never declare or set exApp or exBook.
    It highlights ExcelCodes and tells me Compile Errror: Sub or Function not defined.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so why do you think that is
    what do you think ExcelCodes represents or stores
    did PootleFlumps code define it somewhere.
    I can't speak for others but Im not psychic (many may argue Psychotic) so you need to post where the fault is, and post code upto that point. adding other code after that is meaningless, and to be honest if I see a large tranche of code stolen from elsewhere with little or no effort to explain the problem then I cant be bothered to wade through the code.

    Pootle's code is usually pretty good, perhaps a leetle terse on comments but each to their own
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    so why do you think that is
    what do you think ExcelCodes represents or stores
    did PootleFlumps code define it somewhere.
    I can't speak for others but Im not psychic (many may argue Psychotic) so you need to post where the fault is, and post code upto that point. adding other code after that is meaningless, and to be honest if I see a large tranche of code stolen from elsewhere with little or no effort to explain the problem then I cant be bothered to wade through the code.

    Pootle's code is usually pretty good, perhaps a leetle terse on comments but each to their own
    Sorry my intentions are not to come across as someone trying to steal someones code and run with it. I saw what this code can do and figured since he posted it public it was okay to use.

    I build my own code (shown below) to run a delete and append query for each of the managers then send the make tables to excel

    Private Sub Command154_Click()
    Dim str_folder As String

    str_folder = " S:\Access\Inventory Reduction\Reports"

    Call Shell("explorer.exe " & str_folder, vbNormalFocus)

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "manager1-8delete" ‘just repeats 8 times for the various managers
    DoCmd.OpenQuery "manager1-8append" ‘again repeats 8 times
    DoCmd.SetWarnings True
    Dim reportname As String
    Dim theFilePath As String

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager1", "S:\Access\Inventory Reduction\Reports\Manager1" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager2", "S:\Access\Inventory Reduction\Reports\manager2" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager3", "S:\Access\Inventory Reduction\Reports\manager3" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager4", "S:\Access\Inventory Reduction\Reports\manager4" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager5", "S:\Access\Inventory Reduction\Reports\manager5" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager6", "S:\Access\Inventory Reduction\Reports\manager6" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager7", "S:\Access\Inventory Reduction\Reports\manager7" & Format(Date, "mm-dd-yyyy") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblmanager8", "S:\Access\Inventory Reduction\Reports\manager8" & Format(Date, "mm-dd-yyyy") & ".xls", True

    End Sub


    I looked through what his code can do and maybe I just need direction on how to make it all work. The code I posted is/was my attempt to use the parts of the code he wrote with mine to add the excel formatting i need.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Poots has posted the code in the hope its usefull
    the problem is that its unlikely that grabbing it and modifying it is going to work unless you fully understand the code

    the coment remains
    so why do you think that is
    what do you think ExcelCodes represents or stores
    did PootleFlumps code define it somewhere.
    as the reported problem is with the line containing ExcelCodes, the question is what is ExcelCodes meant to contain

    do you use 'option explicit' as the first line of any code module
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    Poots has posted the code in the hope its usefull
    the problem is that its unlikely that grabbing it and modifying it is going to work unless you fully understand the code

    the coment remains


    as the reported problem is with the line containing ExcelCodes, the question is what is ExcelCodes meant to contain

    do you use 'option explicit' as the first line of any code module
    Im not 100% sure what you're asking me. I will go and do some more reading.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Your code is halting claiming it doesn't know anything about excelcodes. Either you have omitted defining what excelcodes is or you have not copied that bit of code containing excelcodes. Also you need to bear in mind that code in the code bank and elsewhere is provided as isμ there are no guarantees. Some times the code is cumulative ie you needsome other bits often common functions for it to work
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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