Inserting names from Excel to SQL Server table (VBA)

Question

I have written a simple code to loop through rows and insert values in column A (that is column containing full name) into an SQL Table. Something like this:

 For i = 1 to LastRow Command.CommandText = "INSERT INTO [TABLE] [Col1] VALUES ('" & Sheets("Sheet1").Cells(i, 1).Value & "')" Next i

Issue arises when we have names like [O'Connell], which obviously creates a Bobby Table issue.

Are there any clever workarounds to avoid this?

Thanks

N.B. The full name is inserted from another SQL Table. In other words, perhaps a little difficult to edit.

Possible duplicate of VBA, ADO.Connection and query parameters — Victor K
– Victor K, Commented Jun 10, 2018 at 2:09

ashleedawg · Accepted Answer · 2018-06-10 02:09:11Z

1

You could use Replace to remove the problem character:

Command.CommandText = "INSERT INTO [TABLE] [Col1] VALUES ('" & _ Replace(Sheets("Sheet1").Cells(i, 1).Value,"'","") & "')"

answered Jun 10, 2018 at 2:09

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Oday Salim Over a year ago

Brilliant... So in theory I can use this to santize all my strings from (') and (;)... Correct?

ashleedawg Over a year ago

@OdaySalim in theory yes. And you can nest them replace(replace(replace(yourstring,"'",""),"*",""),".","") etc

Oday Salim Over a year ago

How would you nest them?

ashleedawg Over a year ago

if there are any " make sure you get those too replace Chr(34) with whatever

Oday Salim Over a year ago

One more question dude, why are you sanitizing the "."? I do not see anything malicious with it?

Collectives™ on Stack Overflow

Inserting names from Excel to SQL Server table (VBA)

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related