Results 1 to 4 of 4

Thread: What's wrong with vbscript?

  1. #1
    Seeker Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    15
    Articles
    0
    Excel Version
    Excel10

    What's wrong with vbscript?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I am trying to run two VBA macros with vbscript without opening the files 1st file named TIME.xlsm has macro inserted time & date in itself other file named WORKED.xlsm has macro which copies row from TIME.xlsm and delete row after that both macros doing fine but before saving & close I am getting errors so I guess something wrong with my vbscript!

    I have uploaded errors pics in zip also!

    Any suggestions would be appreciated!

    Thanks,
    Attached Files Attached Files

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    254
    Articles
    0
    Excel Version
    2007
    .
    This VBS successfully ran the macro in the workbook TIME.XLSM

    Code:
    'Input Excel File's Full Path  ExcelFilePath = "C:\Users\gagli\Desktop\Time.xlsm"
    
    
    'Input Module/Macro name within the Excel File
      MacroPath = "Module1.InsertDate"
    
    
    'Create an instance of Excel
      Set ExcelApp = CreateObject("Excel.Application")
    
    
    'Do you want this Excel instance to be visible?
      ExcelApp.Visible = True  'or "False"
    
    
    'Prevent any App Launch Alerts (ie Update External Links)
      ExcelApp.DisplayAlerts = False
    
    
    'Open Excel File
      Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
    
    
    'Execute Macro Code
      ExcelApp.Run MacroPath
    
    
    'Save Excel File (if applicable)
      wb.Save
    
    
    'Reset Display Alerts Before Closing
      ExcelApp.DisplayAlerts = True
    
    
    'Close Excel File
      wb.Close
    
    
    'End instance of Excel
      ExcelApp.Quit
    
    
    'Leaves an onscreen message!
      MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation
    If you were to add to this script by repeating the process to open the Workbook WORKED.XLSM and run the macro
    there, your complete process for both workbooks would be realized.

  3. #3
    Seeker Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    15
    Articles
    0
    Excel Version
    Excel10
    Hi,
    Thx for reply,

    Actually problem is not running the macro
    after that saving & closing the file is

    Now I'm facing this one error in new data file & this comes right before saving both files!
    Attached Files Attached Files

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    254
    Articles
    0
    Excel Version
    2007
    Code:
    Set objExcel = CreateObject("Excel.Application")objExcel.Application.Run "'C:\Users\gagli\desktop\DATA\TIME.xlsm'!Module1.InsertDate"
    objExcel.Application.Run "'C:\Users\gagli\desktop\DATA\WORKED.xlsm'!Module1.Copy"
    objExcel.Application.Visible = True
    objExcel.Application.DisplayAlerts = True
    objExcel.Workbooks("TIME.xlsm").Saved = True 
    objExcel.Workbooks("TIME.xlsm").Close 
    objExcel.Workbooks("WORKED.xlsm").Saved = True 
    objExcel.Workbooks("WORKED.xlsm").Close 
    objExcel.Application.Quit

Tags for this Thread

Posting Permissions

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