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!