Trouble writing to Excel using Macro Scheduler

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Ref01010
Newbie
Posts: 5
Joined: Mon Sep 06, 2021 11:09 pm

Trouble writing to Excel using Macro Scheduler

Post by Ref01010 » Wed Sep 15, 2021 11:14 pm

I am having trouble writing to an Excel spreadsheet using Macro Scheduler.
I have a spreadsheet with a list of names that I want to check if they exist already in my system.
I have a macro that will send one name at a time from the spreadsheet and ask me if it is in the system.
I would like the macro to write something like “exists in system” or “add to system” (depending on my response) into a cell in the spreadsheet beside the name.
The macro seems to work, except for the part where it should connect to Excel and write the text. I get a compilation error “Expected end of statement” on the “Dim wbook As Workbook” line.
I’m a newbie and self-taught. I’ve managed to write a few macros that work, but this part doesn’t seem to work no matter what I try. You can see some of the commented-out code that I've tried already.

I would be glad of any advice you can give.

Code: Select all

VBSTART
Sub MarkContactToAdd

Dim xlApp
Dim wbook As Workbook
Dim xlSheet As Worksheet

Set xlApp = CreateObject("Excel.Application")
'Set wbook = xlApp.Workbooks("Leads1.xlsx")
Set wbook = Workbooks("D: \Leads1.xlsx")
Set xlSheet = wbook.Worksheets(1)
'Set wbook = Workbooks("Leads1.xlsx")
'Set xlSheet = wbook.Worksheets("Sheet1")

xlApp.visible = True
'optionally make the sheet visible
xlSheet.Application.Visible = True
xlSheet.Cells(row,12).Value = "Add this contact"

'xlApp.Quit

'Set xlSheet = Nothing
'Set xlBook = Nothing
'Set xlApp = Nothing
End Sub
VBEND

   //Ask how many rows we should get
   Input>maxrows,How many rows shall I get?,1

   If>maxrows=0
     Exit>0
  Endif
   Input>startrow,What row should I start on?,1
   If>startrow=0
     Exit>0
  Endif
   Let lastrow=maxrows+startrow
   //Data starts on row startrow
   Let>row=startrow
   Repeat>row
      //get the name from second column for each row
      Wait>0.05
      XLGetCell>xlBook,Sheet1,row,2,Name

      Wait>0.5


   Let>MSG_XPOS=750
   Let>MSG_YPOS=700
         MessageModal>close this box after clicking in search bar
         Wait>2.00
      Let>SK_DELAY=20
      Send>Name
      Wait>.1
 Ask> Is the contact in the CRM?,Answer

IF>%Answer%=YES,MarkYes
//If NO, Mark that contact is not in the CRM
VBRun>MarkContactToAdd
GoTo>SkipYes
Label>MarkYes
//ActiveCell.FormulaR1C1 = “In CRM already”
Label>SkipYes
      Let>row=row+1
   Until>row>lastrow
 MessageModal>DONE!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Trouble writing to Excel using Macro Scheduler

Post by Dorian (MJT support) » Thu Sep 16, 2021 8:08 am

I'm not able to help with vbscript, but whenever you're working with Excel it's far easier to use the native Excel commands. I recommend reading about XLGetCell for reading cells, XLSetCell for writing to cells, etc.

You can find all the Excel commands here.
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts