Solved

Update"SQL  query"  in Excel Macro

Posted on 2011-09-26
32
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 32

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 32

Expert Comment

by:awking00
ID: 36600340
What happens if you put a space between the double quotes and the word WHERE?
0
 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

733 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