Newbie VB Script - Please Help

[sue_mc]

Hi

I am fairly new to macro scheduler and a total novice with VB Script.

I am trying to open an excel file ask the user a question and send back the answer to excel so that microsft query will refresh the data based on the answer. I know my script is totally incorrect any advice given would be much appreciated


VBSTART
sub customerno()
dim xlapp
dim xlbook
dim xlsheet

set xlapp = CreateObject("Excel.Application")
set xlsheet = xlBook.Worksheets("Cust Hist")




ASK>What Customer would you like? -
input>custno



Label>ContinueON

Wait>0.5
send>%custno%
Wait>0.5
Press Enter

wait>10

[pgriffin]

You can find some useful information if you search the forum.

Try this first:

http://www.mjtnet.com/usergroup/viewtop ... ript+excel

The example should give you a good starting point. I found 28 matches wehn I entered 'VBScript AND Excel ' (leave off the single quotes).

[sue_mc]

Thank you for your reply, I had looked at this example, however what I am trying to do is ask the user a question and return the answer to a specified cell in excel. I have no trouble with the input box in asking the question however I can not get it to return the answer to the specified cell.


thanks again

[Marcus Tettmar]

Sue,

You must not be seeing what I am seeing because the example that SkunkWorks posted shows clearly how to set a cell in Excel. It doesn't have anything to do with asking for input from the user. It answers the exact question you had - how to put a value in a cell.

http://www.mjtnet.com/forum/viewtopic.php?t=1470

Note this line:

xlSheet.Cells(1,1).Value = "This is the column A, row 1"

You are also mixing up VBScript with MacroScript. Plus you seem to have the Ask and Input functions confused with the wrong number of parameters on each. You want something like this:

Code: Select all

VBSTART
Sub CustomerNo(custno)
  dim xlApp
  dim xlBook
  dim xlSheet

  set xlApp = CreateObject("Excel.Application")
  set xlBook = xlApp.WorkBooks.Open("c:\myxlfile.xls")
  set xlSheet = xlBook.Worksheets("Cust Hist")

  xlSheet.Cells(1,1).Value = custno

  xlSheet.Save
  slApp.quit
End Sub
VBEND

Input>custno,Enter Customer Number:
VBRun>CustomerNo,custno

Note the VBScript Subroutine which takes a customer number as a parameter. This opens the XL workbook file (you had that part missing too), sets the sheet and then sets cell 1,1 (A1) to the value of the customer number. Then saves the sheet and quits.

So we have the VBScript ready to be called. Now the Input command takes a customer number and then the script calls the VBScript subroutine, passing this customer number to it.