Solved

Update"SQL  query"  in Excel Macro

Posted on 2011-09-26
32
219 Views
Last Modified: 2012-06-21
Can any one check what is wrong with the SQL squery syntax in Excel Macro
I am getting the syntax error.

Please note Ark means sheet.

Urgently required
Sub Ark2_Button1_Click()
 For i = 1 To 1052

   Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" &_
                        "WHERE EventID = <eventid> "
Next

   Ark2.Activate
End Sub

Open in new window

0
Comment
Question by:Ammar Iqbal
  • 15
  • 9
  • 6
  • +1
32 Comments
 
LVL 7

Expert Comment

by:BusyMama
ID: 36599955
I think you need a space after the last ampersand (before "WHERE EventID).
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36599961
how ?
0
 
LVL 7

Accepted Solution

by:
BusyMama earned 500 total points
ID: 36600007
Literally, a space after the last & sign in your code.  See attached.
Sub Ark2_Button1_Click()
 For i = 1 To 1052

   Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" & _
                        "WHERE EventID = <eventid> "
Next

   Ark2.Activate
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 36600021
I think BusyMama wants you to change
... & "'" &_
                        "WHERE EventID = <eventid> "
to this
 & "'" & _
                        "WHERE EventID = <eventid> "
with a space after the ampersand.
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600034
i think i have done the same. Please  inidicate the exact place .
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600051
not figuring out . help please
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36600052
In Line 7 of the code you provided, you have

&_

instead of

& _

No space in the example you provided, space in the response awking00 and I provided.
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600060
i need to exceute this query as early as possible
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600073
i have done this, but   when i click debug,  the  debug arrow points to "Where" clause
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36600084
Please post the code with the change you made.  Mine debugs without error on my machine.
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600091
here it is
Sub Button1_Click()
  For i = 1 To 1052

   Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" & _
                        "WHERE EventID = <eventid> "
 Next

   Ark2.Activate

End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36600116
My apologies, it debugs for me so I'm at a loss.  My only suggestion would be to save it, close and re-open and see if you continue to have the same error message.

Perhaps awking00 is still following the thread and can jump in.
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600140
is the syntax right?
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600162
Whenever i click  "Run", it says "object required" and two options debug and end
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600169
to be precise run time error 424
0
 
LVL 31

Expert Comment

by:awking00
ID: 36600340
What happens if you put a space between the double quotes and the word WHERE?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Expert Comment

by:BusyMama
ID: 36600419
What is <eventid> and does Excel know where to get it?
0
 
LVL 2

Expert Comment

by:jan24
ID: 36600535
If you're getting "Object Required" it implies to me that it thinks you are treating something as an object that isn't an object.

So my hunch would be that this is some problem with Ark1 and/or Ark2.

Do you have anywhere lines that defines what Ark1 and Ark2 are?  
... If yes, please can you post the full code (or attach the workbook) including those lines.  
... If no, then you will need to add lines to define them.  Let us know what you are trying to achieve and we'll help you define them.
0
 
LVL 2

Expert Comment

by:jan24
ID: 36600585
Actually, thinking about it perhaps you have worksheets that are called Ark1 and Ark2, right?
In which case instead of Ark2.Cells you should put ThisWorkbook.Worksheets("Ark2").Cells

This can be a bit longwinded, so if you want to refer to these worksheets multiple times then you can try this approach:
Sub Button1_Click()
    Dim Ark1 As Worksheet
    Dim Ark2 As Worksheet
    
    Set Ark1 = ThisWorkbook.Worksheets("Ark1")
    Set Ark2 = ThisWorkbook.Worksheets("Ark2")
  
    For i = 1 To 1052



        Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" & _
                        "WHERE EventID = <eventid> "
    Next

    Ark2.Activate

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600601
It goes fine, Can you send me the syntax  of insert query in excel macro. I want to  Insert First name, lastname, email and company name in EventAttendees. using excel macro.

Need to genrerate this query.

Exact Syntax
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36600666
You have the update query syntax fine:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

But the code is looking for an "object".  So maybe you need to tell it (define) the database, the table, the Ark1 variable, or something else it needs in order to run.

You also need to Dim i as Integer if you haven't already done so.
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600678
Update is working fine, can you please temll me the exact syntax of insert query in macro
0
 
LVL 2

Expert Comment

by:jan24
ID: 36600711
This link gives you the syntax of INSERT INTO, along with some worked examples.
If you're still stuck let us know!

http://www.w3schools.com/sql/sql_insert.asp
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36600739
exact syntax  of insert query in Excel macro. There  are amny records in excel which i have to uplaod in database, so i need the exact syntax of insert query in excel macro as early as possible.
Thank you
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36600878
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
0
 
LVL 2

Expert Comment

by:jan24
ID: 36600974
For your particular situation, here's a modified VBA code with an INSERT INTO.  Let me know if this works!
Sub Button1_Click()
    Dim Ark1 As Worksheet
    Dim Ark2 As Worksheet
    
    Set Ark1 = ThisWorkbook.Worksheets("Ark1")
    Set Ark2 = ThisWorkbook.Worksheets("Ark2")
  
    For i = 1 To 1052



        Ark2.Cells(i, 1) = "INSERT INTO EventAttendees (FirstName, LastName, Email, " & _
                       "CompanyName) VALUES (" & _
                       Replace(Ark1.Cells(i, 1), "'", "''") & ", " & _
                       Replace(Ark1.Cells(i, 2), "'", "''") & ", " & _
                       Replace(Ark1.Cells(i, 3), "'", "''") & ", " & _
                       Replace(Ark1.Cells(i, 4), "'", "''") & ") "
    Next

    Ark2.Activate

End Sub 

Open in new window

0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36601554

It works how to nisert  EventID  in  this query.  it is not present in sheet 1  but i will add it later. For now, i need t oadd one column in this query   which is event ID
0
 
LVL 2

Expert Comment

by:jan24
ID: 36601574
As an alternative, if you are dealing with text strings in your data then you might want to wrap the values in inverted commas.   If you do then try this:
Sub Button1_Click()
    Dim Ark1 As Worksheet
    Dim Ark2 As Worksheet
    
    Set Ark1 = ThisWorkbook.Worksheets("Ark1")
    Set Ark2 = ThisWorkbook.Worksheets("Ark2")
  
    For i = 1 To 1052



        Ark2.Cells(i, 1) = "INSERT INTO EventAttendees (FirstName, LastName, Email, " & _
                       "CompanyName) VALUES (" & _
                       "'" & Replace(Ark1.Cells(i, 1), "'", "''") & "', " & _
                       "'" & Replace(Ark1.Cells(i, 2), "'", "''") & "', " & _
                       "'" & Replace(Ark1.Cells(i, 3), "'", "''") & "', " & _
                       "'" & Replace(Ark1.Cells(i, 4), "'", "''") & "') "
    Next

    Ark2.Activate

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36601599
the point is that i will  to exceute these 1052 queries in  the database, or in other words  i will insert1052 records  in  db.  So i need to form a template/macro  query  over here. this exceutes fine, but i want t oadd some more columns which is not present in sheet 1, but in db.
 for example ,Need to add 'EventId' in this insert query
0
 
LVL 1

Author Comment

by:Ammar Iqbal
ID: 36601627
is this the way to do it ? Please correct it
For i = 1 To 1052



        Sheet3.Cells(i, 1) = "INSERT INTO EventAttendees (FirstName, LastName, Email, " & _
                       "CompanyName, EventID) VALUES (" & _
                       Replace(Sheet1.Cells(i, 1), "'", "''") & ", " & _
                       Replace(Sheet1.Cells(i, 2), "'", "''") & ", " & _
                       Replace(Sheet1.Cells(i, 3), "'", "''") & ", " & _
                       Replace(Sheet1.Cells(i, 4), "'", "''") & ") " & _
                       <EventID>
                       
    Next

    Sheet3.Activate

Open in new window

0
 
LVL 2

Expert Comment

by:jan24
ID: 36601679
Hi Ammarr77,
Is it just eventid you're talking about, or other fields too?
I'm guessing that eventid is supposed to be a unique id.  If so then probably the best way is not to have it in the INSERT INTO query at all.  Instead edit your database and set the table to automatically generate a new eventid automatically for you, in the background.  That way you can be sure you get a unique id.
You do this by turning on the AUTO_INCREMENT feature for that field.  The link below explains this further:
http://www.w3schools.com/sql/sql_autoincrement.asp
Exactly how this is done will depend on the database system you are using.  If you have a problem with that then I suggest you close this question, and start a new one under the category for that database system, because at this stage it is no longer anything to do with Excel and you need to get in touch with a different group of experts!
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36709180
Jan24 is right, the type of database might make a big difference.  You can copy/paste easily from Excel into Access, or use many other methods that are quicker and probably easier depending on the tools you have.  Also, if it's a one-time thing, or if it is going to be something that becomes a new business process would make a difference how you should approach the solution.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now