+ Reply to Thread
Results 1 to 1 of 1

Running excel macro always display this message "A file named 'sample.xlsm' already exist

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-17-2011
    Location
    PH
    MS-Off Ver
    Excel 2007
    Posts
    183

    Running excel macro always display this message "A file named 'sample.xlsm' already exist

    Hi Expert,

    I have an excel macro that call by VB script using task manager. a message always appear or display.
    This is the message A file named 'sample.xlsm' already exist in this location. do you want to replace it? or
    Do you want to save the changes you made to 'sample.xlsm;? Yes, No, Cancel.

    If I run the macro through excel it is working, it doesnt receivend anything like this message.

    May I ask what's the problem with my macro

    Here is the excel macro code that call by VB script.

    VB Script code
    Dim xlApp
    Dim xlBook
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Desktop\Trial Prod Dashboard\Production.xlsm", 0, False)
    
    
    xlApp.DisplayAlerts = False
    xlApp.Visible = False
    
    xlApp.Run "GetFile"
    
    xlBook.Saved = True
    xlBook.Save
    
    xlBook.Close
    xlApp.Quit
    
    
    Set xlBook = Nothing
    Set xlApp = Nothing

    Macro Code
    Sub GetFile()
    
        Dim TARFIL, Line, Shift As String
        Dim wb As Workbook, wbtemp As Workbook
        'wbReport1, wbReport2, wbReport3, wbDateRef As Workbook
        Dim ws As Worksheet, wstemp As Worksheet, _
            wsReport1 As Worksheet, wsReport2 As Worksheet, _
            wsReport3 As Worksheet, wsDateRef As Worksheet
        Dim lrow As Long
        
        Set wbtemp = ThisWorkbook    'Raw Data Sheet
        Set wsReport1 = wbtemp.Sheets("REPORT1")
        Set wsReport2 = wbtemp.Sheets("REPORT2")
        Set wsReport3 = wbtemp.Sheets("REPORT3")
        Set wsDateRef = wbtemp.Sheets("RYAN")
    
    
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        
        'Setup date as reference per slides
        With wsDateRef.Range("R1")
           .Value = "Updated as of:  " & Format(Now, "mmmm  dd, yyyy hh:mm AM/PM")
        End With
    
        
        'Read Data source based on Shift/cut off
        If Hour(Now()) >= 6 And Hour(Now()) < 14 Then
           Shift = "A-Shift"
           Set wstemp = wbtemp.Sheets("A-Shift (raw data)")
           TARFIL = "C:\35 - Indust Eng\Inquiry.csv"
    
           'Set wsReport1 = wbReport1.Sheets("REPORT1")
           'Set wsReport2 = wbReport2.Sheets("REPORT2")
           'Set wsReport3 = wbReport2.Sheets("REPORT3")
        ElseIf Hour(Now()) >= 14 And Hour(Now()) < 22 Then
           Shift = "B-Shift"
           Set wstemp = wbtemp.Sheets("B-Shift (raw data)")
           TARFIL = "C:\35 - Indust Eng\Inquiry.csv"
    
          ' Set wsReport1 = wbReport1.Sheets("REPORT1")
           'Set wsReport2 = wbReport2.Sheets("REPORT2")
          ' Set wsReport3 = wbReport2.Sheets("REPORT3")
        End If
         
        
        'opening of source data
        If Dir(TARFIL) <> "" Then
        
            Workbooks.OpenText Filename:=TARFIL, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
            , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
            Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _
            Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1))
        
         
            'Activate Data Source
            Set wb = ActiveWorkbook
            Set ws = wb.Sheets(1)
            
            'Dumping of data to template (Working File)
            With ws
                lrow = .Range("A" & Rows.Count).End(xlUp).Row
                .Columns("F:F").Insert xlToRight
                .Range("F2:F" & lrow).Formula = "=A2&E2"
                .Range("F2:F" & lrow).Value = .Range("F2:F" & lrow).Value
                
                lrow = .Range("A" & Rows.Count).End(xlUp).Row
                .Columns("O:O").Insert xlToRight
                .Range("O2:O" & lrow).Formula = "=A2&E2&J2&N2"
                .Range("O2:O" & lrow).Value = .Range("O2:O" & lrow).Value
                .Range("W2:W" & lrow).Value = Shift
                .Range("A4:V" & lrow).Copy wstemp.Range("A1")
            End With
        
        
            'closing of data Source
            wb.Close
      
            ProductionSchedule
            wbtemp.RefreshAll
        
        End If
        
    End Sub
    
    Private Sub ProductionSchedule()
    
    Dim wbUPH, wbTP As Workbook
    Dim wsUPH, wsTP As Worksheet
    Dim rng, cel As Range
    Dim Uphpath, date_find As String
    
    Uphpath = "C:\Users\NPD\Desktop\Dashboard\Plan.xlsx"
    
    With Application
        .DisplayAlerts = False
        .AskToUpdateLinks = False
    End With
    
    Set wbUPH = Workbooks.Open(Uphpath)
    Set wsUPH = wbUPH.Sheets("ProdSched")
    
    
    Set wbTP = ThisWorkbook
    Set wsTP = wbTP.Sheets("UPHReference")
    
    
    'Set the column as ease
    'wsTP.Columns("A:F").Delete xlToLeft
    wsTP.Cells.UnMerge
    wsTP.Columns("A:F").ClearContents
    
    
    With wsUPH.Range("AP8:AP39")
        .AutoFilter Field:=1, Criteria1:="<>"
        .SpecialCells(xlCellTypeVisible).Copy wsTP.Range("A1")
    End With
    
    Set rng = wsUPH.Range("AP8:YZ8")
    date_find = Format(Now(), "m/d/yyyy")
    Set cel = rng.Find(What:=date_find, After:=wsUPH.Range("AP8"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
    'Debug.Print rng.Address
    'Debug.Print date_find
    
    'wsUPH.Cells.MergeCells = False
    If Not cel Is Nothing Then
       wsUPH.Range(Cells(8, cel.Column), Cells(39, cel.Column + 5)).SpecialCells(xlCellTypeVisible).Copy wsTP.Range("B1")
    End If
    
    
    wsUPH.AutoFilterMode = False
    wbUPH.Close
    
    
    With Application
        .DisplayAlerts = True
    End With
    
    
    End Sub
    when I click the No, this is the error message appear from Windows Script Host
    Dashboard\Runtask.vbs
    Line: 14
    Char: 1
    Error: Save method of workbook class failed
    Code:
    source code: Microsoft excel
    Last edited by Jovillanueva; 01-28-2014 at 02:29 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Display a message while a macro is running
    By vbace2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2015, 09:13 AM
  2. [SOLVED] Macro Pivot Table: Most elegant way to skip a "do not display" if element doesn't exist
    By Skotzmun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2013, 05:03 AM
  3. Saving xlsm file as xlsx while running macro
    By happygilmore in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2012, 02:32 PM
  4. Inhibit "File Exist" Message
    By DCSwearingen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2008, 09:40 AM
  5. [SOLVED] Checking that file exist and if not display a message
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2006, 10:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1