Expert: Miguel Zapico - 10/4/2006

Question
Hello,

I am trying to open an excel worksheet, paste data that will be in the clipboard to sheet 2.  Then copy the data from D1-D3, move to sheet 1, paste the data in C3, print sheet 1, and close the excel worksheet WITHOUT saving the changes.  Here is my script, which is not working for me.  I am using Macro Scheduler by MJNet.  

VBSTART
Option Explicit
Sub QTTankReport

Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("c:My DocumentsQT Tank Gauge.xls")
Set xlSheet = xlBook.Worksheets("Sheet2")

Range("D1:D46").Copy
set xlSheet = xlBook.Worksheets("Sheet1")
Range("C3").Select
Paste

PrintOut Copies=1, Collate=True
wb.Close False

End Sub

Answer
I have tweaked the script, the main issue is that you are declaring and setting the objects but you are not using them afterwards.  See also some comments on the body.

I have done this with Notepad:

VBSCRIPT
QTTankReport 'Call the sub

Sub QTTankReport

Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("c:\My Documents\QT Tank Gauge.xls")
Set xlSheet = xlBook.Worksheets("Sheet2")
xlapp.visible = true 'For debug purposes, show the Excel window

xlSheet.Range("D1:D46").Copy
set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Activate 'The sheet needs to be active
xlSheet.Range("C3").Select
xlSheet.Paste

xlSheet.PrintOut , , 1, , , , True 'In the script, better use the long format
xlBook.Close False

End Sub

Hope this helps,
Miguel