• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

EXCEL, MICROSOFT, 2000, COPY FROM SECOND ROW ONLY INTO SQL

folks

I have a range that picks up colum a and dumps it into sql server

Public Sub DoInsert()
  Dim r As Range

  Set r = Application.Intersect(Sheet1.Range("A:A"), Sheet1.UsedRange)

  Dim sql As String
  Dim c As Range
  For Each c In r
    sql = sql & " INSERT INTO dummy (PROJECT) values ('" & c.Value2 & "') " & vbCrLf

how do I firstly make  macro copy in values from colum b as well into the above script ?

all help will do

r
0
rutgermons
Asked:
rutgermons
  • 2
2 Solutions
 
JeggburtCommented:
sql = sql & " INSERT INTO dummy (PROJECT) values ('" & c.Value2 & "', '" & c.offset(0, 1).Value2 & "') " & vbCrL
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

Public Sub DoInsert()
  Dim r As Range

  Set r = Application.Intersect(Sheet1.Range("A:A"), Sheet1.UsedRange)

  Dim sql As String
  Dim c As Range
  For Each c In r
    sql = sql & " INSERT INTO dummy (PROJECT, otherfield) values ('" & c.Value2 & "','" & c.Offset(0,1).Value2 & "' ) " & vbCrLf
  next
... rest of code ...
0
 
rutgermonsAuthor Commented:
thanks guys

if i run

sql = sql & "INSERT INTO dummy values('" & c.Value2 & "','" & c.Offset(0, 1).Value2 & "')" & vbCrLf

it works
however if I run

sql = sql & "INSERT INTO dummy (project,desc)values('" & c.Value2 & "','" & c.Offset(0, 1).Value2 & "')" & vbCrLf

then I get an error : incorrect syntax near the keyword 'desc'

could anyone advise?

also,how can I start on row 2,thus I can add headings in the first rows
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
desc is a reserved keyword:


sql = sql & "INSERT INTO dummy (project, [desc] )values('" & c.Value2 & "','" & c.Offset(0, 1).Value2 & "')" & vbCrLf
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now