Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Windows 7 Task Scheduler VB not calling excel macro but does when manually run

    Hi All,

    I have moved form a windows XP machine to window 7 and now have problems with the below. I have a VB script running which then call an excel macro, however, the excel does not work when running on the Task Scheduler, it does run fine when I run the VB script myself.

    I have read various forums for ages but do not seem to fund the answer.

    When it runs form scheduler it leave the excel,exe file open in processes but thatís it.

    Appreciate any help you can give me.


    Dim objExcel As Object
    Dim objBook As Object
    Dim MACRO As String

    Sub Main
    Call EXCEL_MACRO
    End Sub

    Function EXCEL_MACRO

    MACRO = "C:\IDEA\IDEA EXCEL MACROS\IDEA_MACROS.xlsm"

    ' Start up Excel

    Set ObjExcel = CreateObject("Excel.Application")
    objExcel.Visible = True

    ' Open an existing workbook containing the macros
    Set objBook = objExcel.Workbooks.Open(MACRO)

    ' Run the imbedded Excel Macro module name cannot be the same!

    objExcel.Application.Run "CAR"

    End Function

    And here is the opening bit of the excel macro:

    Sub CAR()

    Dim lastline

    Path = "\\UKFILE01\CKSCCA$\Management\Reporting\Deans_Rep orts\Credit Limit\"

    ' Check to see if file exists

    If Dir(Path & "RELEASED ON CREDIT- " & Format(Now, "DD-MM-YYYY") & ".XLS") <> "" Then

    ' Open the data workbook

    Workbooks.Open Filename:=Path & "RELEASED ON CREDIT- " & Format(Now, "DD-MM-YYYY") & ".XLS"

    '
    ' CAR Macro
    '

    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D1").Select
    Selection.End(xlDown).Select
    lastline = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.Formula = "=sum(D2" & lastline & ")"
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlDouble
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.AutoFill Destination:=Range("D" & (lastline + 2) & ":O" & (lastline + 2)), Type:=xlFillDefault

    lRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

    Range("A1:O" & lRow).Sort _
    Key1:=Range("F1"), Order1:=xlDescending, Header:=xlYes
    Range("A1").Select

    ' Save
    '
    ActiveWorkbook.Save
    ActiveWindow.Close

    End If

    ' Quit
    '
    Excel.Application.Quit

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not clear exactly what the situation is or the issue. Could you elaborate? You seem to be discussing 3 things: Scheduler, VB, and an excel macro (VBA). If the issue is the excel (VBA) macro, this is the appropriate place, but if the issue is VB or the scheduler it may be outside the expertise of the people who frequent this board.

    How is the scheduler calling the excel macro? Typically you would have a Workbook_Open in the excel workbook which calls the routine you want to run. You would then have he scheduler open the particular workbook with the macro in it. Also if you are running the excel macro from VB you may have to preface the lines to indicate that you are using VBA lines rather than VB lines (the excel lines are part of objExcel application or the objBook object.

    I have no experience with VB, but I would suspect not doing this would have caused compile or run-time errors in VB.

    Steve

Posting Permissions

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