Macro to copy an image and create a file does not work when run automatically through VBS

While vbscript is similar to VBA, this is a VBA macros forum. Why not use VBA? If this were, I would say post the code.

For those sorts of things, I put a popup msgbox in the open event. If no response, it closes the Windows Scheduler workbook, runs the macro, and closes the workbook. If I respond to the msgbox, it skips the macro so I can edit the file.

e.g. In ThisWorbook object:

Private Sub Workbook_Open()
'ozgrid.com #43022, Bob Phillips
  Dim cTime As Long
  Dim wsh As Object
   
  Set wsh = CreateObject("WScript.Shell")
  cTime = 15 ' 15 secs
  Select Case wsh.Popup("Test?", cTime, "Question - Times Out in 15s", vbOKCancel)
    Case vbOK
        MsgBox "You clicked OK"
    Case vbCancel
        MsgBox "You clicked Cancel"
    Case -1
        MsgBox "Timed out"
        'comment out above and add code here if you want to do stuff before close.
        ThisWorkbook.Close
    Case Else
  End Select
End Sub

Hi

Thanks a lot !

Let me send you the code I am using in vba . The problem is when this macro is called through VBS file the image is not created specially when run in night when the task is scheduled. The code is as follows

Public Sub CreateScreenshot()
 
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim ChartName As String
Dim imgPath As String
Dim NSheetName As String, RngFrm As String, RngTo As String
Dim wb As Workbook
Dim WB1 As Workbook
Dim AttachScreenshot As String
Dim ImgFilePath As String
Dim AttachFile As String
Set wb = ThisWorkbook
AttachFile = wb.Worksheets("Mail Content").Range("D6").Value
NSheetName = wb.Worksheets("Mail Content").Range("E10").Value
RngFrm = wb.Worksheets("Mail Content").Range("F10").Value
RngTo = wb.Worksheets("Mail Content").Range("G10").Value
AttachScreenshot = wb.Worksheets("Mail Content").Range("H10").Value
ImgFilePath = "C:\MailScheduler_Files\" & Worksheets("Mail Content").Range("D14").Value
tmpImageName = ImgFilePath & "\ScreenShot.jpg"
SaveExt = "." & Right(AttachFile, Len(AttachFile) - InStrRev(AttachFile, "."))
AttachfileName = Right(AttachFile, Len(AttachFile) - InStrRev(AttachFile, "\"))
 
'Clearing the Office Clipboard
    Dim oData   As New DataObject 'object to use the clipboard
    oData.Clear
    oData.SetText Text:=Empty 'Clear
'    oData.PutInClipboard 'take in the clipboard to empty it
' Clear Clipboard
 
 
'Check if Folder Exists - if not then create a folder and save image there.
Folder = Dir(ImgFilePath, vbDirectory)
 
    If Folder = vbNullString Then
        VBA.FileSystem.MkDir (ImgFilePath)
    End If
   
    'Remove existing image to create new image
    If FileExists(tmpImageName) Then
        ' First remove readonly attribute, if set
        SetAttr tmpImageName, vbNormal
        ' Then delete the file
        Kill tmpImageName
    End If
 
'Refresh  file and get new screenshot saved in the given path
Set WB1 = Workbooks.Open(AttachFile)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
WB1.Unprotect
WB1.RefreshAll
 
'Range to save as an image
 
On Error Resume Next
Set rangetosend = WB1.Worksheets(NSheetName).Range(RngFrm & ":" & RngTo)
Call Clear_Clipboard
WB1.Worksheets(NSheetName).Range(RngFrm & ":" & RngTo).CopyPicture Appearance:=xlPrinter, Format:=xlPicture
  'wait until the clipboard gets a pic, but not over 9 seconds (avoid infinite loop)
                    T = Timer
                    Do
                          Waiting (10)
                    Loop Until Is_Pic_in_Clipboard Or Timer - T > 0.9
' Application.Wait (Now + TimeValue("0:00:10"))
    If Not GetWorksheet("TempImage") Is Nothing Then
        WB1.Worksheets("TempImage").Delete
    End If
WB1.Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "TempImage"
Dim sht As Worksheet
Set sht = WB1.Worksheets("TempImage")
sht.Shapes.AddChart
sht.Shapes.Item(1).Select
Set objChart = ActiveChart
'On Error Resume Next
With objChart
    .ChartArea.Height = rangetosend.Height
    .ChartArea.Width = rangetosend.Width
    .ChartArea.Fill.Visible = msoFalse
    .ChartArea.Border.LineStyle = xlLineStyleNone
    .Paste
     Application.Wait (Now + TimeValue("0:00:10"))
     Application.CutCopyMode = False
End With
 
sht.Shapes("Chart 1").Name = "ScreenshotN"
'sht.Shapes.Item(1).Select
tmpImageName = ImgFilePath & "\ScreenShot.jpg"
sht.Shapes("screenshotN").Select
Set objChart = ActiveChart
'On Error Resume Next
With objChart
.Export FileName:=tmpImageName, Filtername:="JPG"
End With
WB1.Worksheets("TempImage").Visible = xlSheetHidden
WB1.Worksheets(NSheetName).Activate
Range("A1").Select
WB1.Save
WB1.Close True
 
End sub