locked
PRINTING & SAVING FROM EXCEL DATABASE ON SPECIFIC SHEET WITH VBA CODE RRS feed

  • General discussion

  • Hi im having error while clicking on the command to print specific report from database.

    Having this line highlighted: ActiveWorkbook.SaveAs Filename:=path & job & "-" & mydate & ".xlsx"

    Please find below full script:

    Private Sub CommandButton1_Click()

    Dim regnumb As String
    Dim make As String
    Dim model As String
    Dim job As String
    Dim day As Date
    Dim manualprintedmilleage As String
    Dim engineoil As String
    Dim engineoilfilter As String
    Dim engineoilleaks As String
    Dim fuelpipesconditionandleaks As String
    Dim exhaustsystem As String
    Dim airintakesystem As String
    Dim allvbelts As String
    Dim vbelttensioners As String
    Dim allclampsandhosesa As String
    Dim enginemountings As String
    Dim fuelfilter As String
    Dim fuelwatertrap As String
    Dim airfilter As String
    Dim turboclearance As String
    Dim valveclearanceadjustment As String
    Dim coolingsystem As String
    Dim antifreezeconcentration As String
    Dim radiatorandheaterhoses As String
    Dim pressuretestcoolingsystem As String
    Dim allclampsandhoses As String
    Dim surgetankcaps As String
    Dim fanhub As String
    Dim radiatorintercoolercondition As String
    Dim cabmountinglocksandcabsusp As String
    Dim cabjacksystemandoillevel As String
    Dim cabventfilter As String
    Dim wiperblades As String
    Dim greaseallgreasenipplesandfithwheel As String
    Dim mountingboltsof5thwheel As String
    Dim steeringfluid As String
    Dim steeringoilfilter As String
    Dim trackrodjointstierodends As String
    Dim draglinkjoints As String
    Dim ujointsteeringshaft As String
    Dim powersteeringpipesandconnections As String
    Dim play As String
    Dim allbolts As String
    Dim torqueuboltsandwheelnuts As String
    Dim springs As String
    Dim springhangerpins As String
    Dim shocksmountings As String
    Dim kingpinplay As String
    Dim frontbearingnoiseandplay As String
    Dim brakeliningthickness As String
    Dim clutchfluid As String
    Dim gearboxoil As String
    Dim gearboxanddiffbreathersfilters As String
    Dim gearleverbushcontrolrodsandadj As String
    Dim bellhousingbolts As String
    Dim gearboxoilleaks As String
    Dim gearboxoutputshaftplay As String
    Dim ptopumpbolts As String
    Dim frontandrearwheelbearingsnoiseandplay As String
    Dim diffoil As String
    Dim inputshaftplay As String
    Dim outputshaftplay As String
    Dim oilleaks As String
    Dim shocksandmountings As String
    Dim scamsystem As String
    Dim suspensionmountingsandslings As String
    Dim torquerods As String
    Dim springsandairbags As String
    Dim brakeliningthicknesslr            As String
    Dim differentialtorqueuboltsandwheelnuts As String
    Dim airdrierfilter As String
    Dim trailercontvalve As String
    Dim drainairtankscheckairleaks As String
    Dim checkloadsensingvalve As String
    Dim faultcodewithdianostica As String
    Dim batterywaterlevelnonsealed As String
    Dim batteryholddownclamps As String
    Dim batteryterminalsandcable As String
    Dim batteryloadtestvalue As String
    Dim checkalllights As String
    Dim engineoiltime As String
    Dim gearboxsoileatonfuchstitancytracldsemisynthetic As String
    Dim diffoiltime As String
    Dim antifreeze As String
    Dim powersteeringoil As String
    Dim brakefluid As String
    Dim r As Long
    Dim path As String
    Dim myfilename As String
    lastrow = Sheets("Summary Report").Range("A" & Rows.Count).End(xlUp).Row
    r = 2
    For r = 2 To lastrow
    If Cells(r, 96).Value = "done" Then GoTo nextrow

    regnumb = Sheets("Summary Report").Cells(r, 1).Value
    make = Sheets("Summary Report").Cells(r, 2).Value
    model = Sheets("Summary Report").Cells(r, 3).Value
    job = Sheets("Summary Report").Cells(r, 4).Value
    day = Sheets("Summary Report").Cells(r, 5).Value
    manualprintedmilleage = Sheets("Summary Report").Cells(r, 6).Value
    engineoil = Sheets("Summary Report").Cells(r, 7).Value
    engineoilfilter = Sheets("Summary Report").Cells(r, 8).Value
    oilleaks = Sheets("Summary Report").Cells(r, 9).Value
    fuelpipesconditionandleaks = Sheets("Summary Report").Cells(r, 10).Value
    exhaustsystem = Sheets("Summary Report").Cells(r, 11).Value
    airintakesystem = Sheets("Summary Report").Cells(r, 12).Value
    allvbelts = Sheets("Summary Report").Cells(r, 13).Value
    vbelttensioners = Sheets("Summary Report").Cells(r, 14).Value
    allclampsandhoses = Sheets("Summary Report").Cells(r, 15).Value
    enginemountings = Sheets("Summary Report").Cells(r, 16).Value
    fuelfilter = Sheets("Summary Report").Cells(r, 17).Value
    fuelwatertrap = Sheets("Summary Report").Cells(r, 18).Value
    airfilter = Sheets("Summary Report").Cells(r, 19).Value
    turboclearance = Sheets("Summary Report").Cells(r, 20).Value
    valveclearanceadjustment = Sheets("Summary Report").Cells(r, 21).Value
    coolingsystem = Sheets("Summary Report").Cells(r, 22).Value
    antifreezeconcentration = Sheets("Summary Report").Cells(r, 23).Value
    radiatorandheaterhoses = Sheets("Summary Report").Cells(r, 24).Value
    pressuretestcoolingsystem = Sheets("Summary Report").Cells(r, 25).Value
    allclampsandhoses = Sheets("Summary Report").Cells(r, 26).Value
    surgetankcaps = Sheets("Summary Report").Cells(r, 27).Value
    fanhub = Sheets("Summary Report").Cells(r, 28).Value
    radiatorintercoolercondition = Sheets("Summary Report").Cells(r, 29).Value
    cabmountinglocksandcabsusp = Sheets("Summary Report").Cells(r, 31).Value
    cabjacksystemandoillevel = Sheets("Summary Report").Cells(r, 32).Value
    cabventfilter = Sheets("Summary Report").Cells(r, 33).Value
    wiperblades = Sheets("Summary Report").Cells(r, 34).Value
    greaseallgreasenipplesandfithwheel = Sheets("Summary Report").Cells(r, 35).Value
    mountingboltsof5thwheel = Sheets("Summary Report").Cells(r, 36).Value
    steeringfluid = Sheets("Summary Report").Cells(r, 38).Value
    steeringoilfilter = Sheets("Summary Report").Cells(r, 39).Value
    trackrodjointstierodends = Sheets("Summary Report").Cells(r, 40).Value
    draglinkjoints = Sheets("Summary Report").Cells(r, 41).Value
    ujointsteeringshaft = Sheets("Summary Report").Cells(r, 42).Value
    powersteeringpipesandconnections = Sheets("Summary Report").Cells(r, 43).Value
    play = Sheets("Summary Report").Cells(r, 45).Value
    allbolts = Sheets("Summary Report").Cells(r, 46).Value
    torqueuboltsandwheelnuts = Sheets("Summary Report").Cells(r, 48).Value
    springs = Sheets("Summary Report").Cells(r, 49).Value
    springhangerpins = Sheets("Summary Report").Cells(r, 50).Value
    shocksmountings = Sheets("Summary Report").Cells(r, 51).Value
    kingpinplay = Sheets("Summary Report").Cells(r, 52).Value
    frontbearingnoiseandplay = Sheets("Summary Report").Cells(r, 53).Value
    brakeliningthickness = Sheets("Summary Report").Cells(r, 54).Value
    clutchfluid = Sheets("Summary Report").Cells(r, 56).Value
    gearboxoil = Sheets("Summary Report").Cells(r, 57).Value
    gearboxanddiffbreathersfilters = Sheets("Summary Report").Cells(r, 58).Value
    gearleverbushcontrolrodsandadj = Sheets("Summary Report").Cells(r, 59).Value
    bellhousingbolts = Sheets("Summary Report").Cells(r, 60).Value
    oilleaks = Sheets("Summary Report").Cells(r, 61).Value
    outputshaftplay = Sheets("Summary Report").Cells(r, 62).Value
    ptopumpbolts = Sheets("Summary Report").Cells(r, 63).Value
    frontandrearwheelbearingsnoiseandplay = Sheets("Summary Report").Cells(r, 65).Value
    diffoil = Sheets("Summary Report").Cells(r, 66).Value
    inputshaftplay = Sheets("Summary Report").Cells(r, 67).Value
    outputshaftplay = Sheets("Summary Report").Cells(r, 68).Value
    oilleaks = Sheets("Summary Report").Cells(r, 69).Value
    shocksandmountings = Sheets("Summary Report").Cells(r, 70).Value
    scamsystem = Sheets("Summary Report").Cells(r, 71).Value
    suspensionmountingsandslings = Sheets("Summary Report").Cells(r, 72).Value
    torquerods = Sheets("Summary Report").Cells(r, 73).Value
    springsandairbags = Sheets("Summary Report").Cells(r, 74).Value
    brakeliningthicknesslr            = Sheets("Summary Report").Cells(r, 75).Value
    torqueuboltsandwheelnuts = Sheets("Summary Report").Cells(r, 76).Value
    airdrierfilter = Sheets("Summary Report").Cells(r, 78).Value
    trailercontvalve = Sheets("Summary Report").Cells(r, 79).Value
    drainairtankscheckairleaks = Sheets("Summary Report").Cells(r, 80).Value
    checkloadsensingvalve = Sheets("Summary Report").Cells(r, 81).Value
    faultcodewithdianostica = Sheets("Summary Report").Cells(r, 83).Value
    batterywaterlevelnonsealed = Sheets("Summary Report").Cells(r, 84).Value
    batteryholddownclamps = Sheets("Summary Report").Cells(r, 85).Value
    batteryterminalsandcable = Sheets("Summary Report").Cells(r, 86).Value
    batteryloadtestvalue = Sheets("Summary Report").Cells(r, 87).Value
    checkalllights = Sheets("Summary Report").Cells(r, 88).Value
    engineoil = Sheets("Summary Report").Cells(r, 90).Value
    gearboxsoileatonfuchstitancytracldsemisynthetic = Sheets("Summary Report").Cells(r, 91).Value
    diffoil = Sheets("Summary Report").Cells(r, 92).Value
    antifreeze = Sheets("Summary Report").Cells(r, 93).Value
    powersteeringoil = Sheets("Summary Report").Cells(r, 94).Value
    brakefluid = Sheets("Summary Report").Cells(r, 95).Value


    Cells(r, 96).Value = "done"
    Application.DisplayAlerts = False
    Workbooks.Open ("C:\Users\imudh\OneDrive - Office Everyday\Desktop\IZDIHAAR\FLEETCARE\REPORTS\TOTAL MAURITIUS REPORT\FLEETMASTER TOTAL REPORT TEMPLATE.xlsx")
    ActiveWorkbook.Sheets("template").Activate
    ActiveWorkbook.Sheets("template").Range("B4").Value = regnumb
    ActiveWorkbook.Sheets("template").Range("B5").Value = make
    ActiveWorkbook.Sheets("template").Range("B6").Value = model
    ActiveWorkbook.Sheets("template").Range("D4").Value = job
    ActiveWorkbook.Sheets("template").Range("D5").Value = day
    ActiveWorkbook.Sheets("template").Range("D6").Value = manualprintedmilleage
    ActiveWorkbook.Sheets("template").Range("C11").Value = engineoil
    ActiveWorkbook.Sheets("template").Range("C12").Value = engineoilfilter
    ActiveWorkbook.Sheets("template").Range("C13").Value = oilleaks
    ActiveWorkbook.Sheets("template").Range("C14").Value = fuelpipesconditionandleaks
    ActiveWorkbook.Sheets("template").Range("C15").Value = exhaustsystem
    ActiveWorkbook.Sheets("template").Range("C16").Value = airintakesystem
    ActiveWorkbook.Sheets("template").Range("C17").Value = allvbelts
    ActiveWorkbook.Sheets("template").Range("C18").Value = vbelttensioners
    ActiveWorkbook.Sheets("template").Range("C19").Value = allclampsandhoses
    ActiveWorkbook.Sheets("template").Range("C20").Value = enginemountings
    ActiveWorkbook.Sheets("template").Range("C21").Value = fuelfilter
    ActiveWorkbook.Sheets("template").Range("C22").Value = fuelwatertrap
    ActiveWorkbook.Sheets("template").Range("C23").Value = airfilter
    ActiveWorkbook.Sheets("template").Range("C24").Value = turboclearance
    ActiveWorkbook.Sheets("template").Range("C25").Value = valveclearanceadjustment
    ActiveWorkbook.Sheets("template").Range("C26").Value = coolingsystem
    ActiveWorkbook.Sheets("template").Range("C27").Value = antifreezeconcentration
    ActiveWorkbook.Sheets("template").Range("C28").Value = radiatorandheaterhoses
    ActiveWorkbook.Sheets("template").Range("C29").Value = pressuretestcoolingsystem
    ActiveWorkbook.Sheets("template").Range("C30").Value = allclampsandhoses
    ActiveWorkbook.Sheets("template").Range("C31").Value = surgetankcaps
    ActiveWorkbook.Sheets("template").Range("C32").Value = fanhub
    ActiveWorkbook.Sheets("template").Range("C33").Value = radiatorintercoolercondition
    ActiveWorkbook.Sheets("template").Range("C35").Value = cabmountinglocksandcabsusp
    ActiveWorkbook.Sheets("template").Range("C36").Value = cabjacksystemandoillevel
    ActiveWorkbook.Sheets("template").Range("C37").Value = cabventfilter
    ActiveWorkbook.Sheets("template").Range("C38").Value = wiperblades
    ActiveWorkbook.Sheets("template").Range("C39").Value = greaseallgreasenipplesandfithwheel
    ActiveWorkbook.Sheets("template").Range("C40").Value = mountingboltsof5thwheel
    ActiveWorkbook.Sheets("template").Range("C42").Value = steeringfluid
    ActiveWorkbook.Sheets("template").Range("C43").Value = steeringoilfilter
    ActiveWorkbook.Sheets("template").Range("C44").Value = trackrodjointstierodends
    ActiveWorkbook.Sheets("template").Range("C45").Value = draglinkjoints
    ActiveWorkbook.Sheets("template").Range("C46").Value = ujointsteeringshaft
    ActiveWorkbook.Sheets("template").Range("C47").Value = powersteeringpipesandconnections
    ActiveWorkbook.Sheets("template").Range("C49").Value = play
    ActiveWorkbook.Sheets("template").Range("C50").Value = allbolts
    ActiveWorkbook.Sheets("template").Range("C52").Value = torqueuboltsandwheelnuts
    ActiveWorkbook.Sheets("template").Range("C53").Value = springs
    ActiveWorkbook.Sheets("template").Range("C54").Value = springhangerpins
    ActiveWorkbook.Sheets("template").Range("C55").Value = shocksmountings
    ActiveWorkbook.Sheets("template").Range("C56").Value = kingpinplay
    ActiveWorkbook.Sheets("template").Range("C57").Value = frontbearingnoiseandplay
    ActiveWorkbook.Sheets("template").Range("C58").Value = brakeliningthickness
    ActiveWorkbook.Sheets("template").Range("C60").Value = clutchfluid
    ActiveWorkbook.Sheets("template").Range("C61").Value = gearboxoil
    ActiveWorkbook.Sheets("template").Range("C62").Value = gearboxanddiffbreathersfilters
    ActiveWorkbook.Sheets("template").Range("C63").Value = gearleverbushcontrolrodsandadj
    ActiveWorkbook.Sheets("template").Range("C64").Value = bellhousingbolts
    ActiveWorkbook.Sheets("template").Range("C65").Value = oilleaks
    ActiveWorkbook.Sheets("template").Range("C66").Value = outputshaftplay
    ActiveWorkbook.Sheets("template").Range("C67").Value = ptopumpbolts
    ActiveWorkbook.Sheets("template").Range("C69").Value = frontandrearwheelbearingsnoiseandplay
    ActiveWorkbook.Sheets("template").Range("C70").Value = diffoil
    ActiveWorkbook.Sheets("template").Range("C71").Value = inputshaftplay
    ActiveWorkbook.Sheets("template").Range("C72").Value = outputshaftplay
    ActiveWorkbook.Sheets("template").Range("C73").Value = oilleaks
    ActiveWorkbook.Sheets("template").Range("C74").Value = shocksandmountings
    ActiveWorkbook.Sheets("template").Range("C75").Value = scamsystem
    ActiveWorkbook.Sheets("template").Range("C76").Value = suspensionmountingsandslings
    ActiveWorkbook.Sheets("template").Range("C77").Value = torquerods
    ActiveWorkbook.Sheets("template").Range("C78").Value = springsandairbags
    ActiveWorkbook.Sheets("template").Range("C79").Value = brakeliningthicknesslr           
    ActiveWorkbook.Sheets("template").Range("C80").Value = torqueuboltsandwheelnuts
    ActiveWorkbook.Sheets("template").Range("C82").Value = airdrierfilter
    ActiveWorkbook.Sheets("template").Range("C83").Value = trailercontvalve
    ActiveWorkbook.Sheets("template").Range("C84").Value = drainairtankscheckairleaks
    ActiveWorkbook.Sheets("template").Range("C85").Value = checkloadsensingvalve
    ActiveWorkbook.Sheets("template").Range("C87").Value = faultcodewithdianostica
    ActiveWorkbook.Sheets("template").Range("C88").Value = batterywaterlevelnonsealed
    ActiveWorkbook.Sheets("template").Range("C89").Value = batteryholddownclamps
    ActiveWorkbook.Sheets("template").Range("C90").Value = batteryterminalsandcable
    ActiveWorkbook.Sheets("template").Range("C91").Value = batteryloadtestvalue
    ActiveWorkbook.Sheets("template").Range("C92").Value = checkalllights
    ActiveWorkbook.Sheets("template").Range("C94").Value = engineoil
    ActiveWorkbook.Sheets("template").Range("C95").Value = gearboxsoileatonfuchstitancytracldsemisynthetic
    ActiveWorkbook.Sheets("template").Range("C96").Value = diffoil
    ActiveWorkbook.Sheets("template").Range("C97").Value = antifreeze
    ActiveWorkbook.Sheets("template").Range("C98").Value = powersteeringoil
    ActiveWorkbook.Sheets("template").Range("C99").Value = brakefluid


    path = "C:\Users\imudh\OneDrive - Office Everyday\Desktop\IZDIHAAR\FLEETCARE\REPORTS\TOTAL MAURITIUS REPORT\TOTAL REPORTS\"
    mydate = Date
    mydate = Format(mydate, "dd_mmm_yyyy")
    ActiveWorkbook.SaveAs Filename:=path & job & "-" & mydate & ".xlsx"
    myfilename = ActiveWorkbook.FullName
    SetAttr myfilename, vbReadOnly
    Application.DisplayAlerts = True
    ActiveWorkbook.PrintOut copies:=1
    ActiveWorkbook.Close SaveChanges:=False



     




    nextrow:
    Next r



    End Sub

    Friday, October 18, 2019 7:18 AM