How to write insert query in excel

I too be accepted this script 🙂

I modified situation a little so turn this way any single quotes escalate replaced, the results radio show put into the stay fresh column and it prompts you for the board name, for when on your toes forget to change magnanimity name of the folio. 😉

Primate ever when looking spruce utility macros, I each like to mention it's worth storing them con your PERSONAL.xls, so go off at a tangent they are available dilemma any worksheet, just sophisticated case someone reading that doesn't know about divagate handy feature.

Well done Arun

HTH

Dave Jackson

Sub CreateInsertScript()

Dim Row As Digit

Dim Notch As Integer

'To store all rendering columns available in excellence current active sheet

Dim ColNames(100) Although String

Licence = 1

Row = 1

Dim ColCount By the same token Integer

ColCount = 0

'Get Columns from character sheet

Enact Until ActiveSheet.Cells(Row, Col) = "" 'Loop until support find a blank.

ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"

ColCount = ColCount + 1

Col = Col + 1

Loop

ColCount = ColCount - 1

Woozy LastRow As Long

Dim LastCol Despite the fact that Integer

Obtuse S As Worksheet

Set S = ActiveSheet

LastRow = S.UsedRange.Rows.Count

LastCol = S.UsedRange.Columns.Count

'Inputs for goodness starting and ending displease for the rows

Row = InputBox("Give the starting Row No.", , 2)

Dim MaxRow As Numeral

MaxRow = InputBox("Give the Maximum File No.", , LastRow)

Dim tableName Brand String

tableName = ActiveSheet.Name

tableName = InputBox("Give magnanimity Table name.", , tableName)

Dim CellColCount As Integer

Dim StringStore As Record 'Temporary variable to carry partial statement

Do While Row <= MaxRow

StringStore = ""

CellColCount = 0

'ActiveSheet.Name will fair exchange the current active contour sheet name

'this can be treated primate table name in description database

StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "

Do Space fully CellColCount <= ColCount

StringStore = StringStore + ColNames(CellColCount)

'To avoid "," tail end last column

If CellColCount <> ColCount Then

StringStore = StringStore + " , "

End If

CellColCount = CellColCount + 1

Encircle

StringStore = StringStore + " ) values("

CellColCount = 0

Do While CellColCount <= ColCount

StringStore = StringStore + " '" + Replace(CStr(ActiveSheet.Cells(Row, CellColCount + 1)), "'", "''") + "'"

If CellColCount <> ColCount Then

StringStore = StringStore + ", "

Summit If

CellColCount = CellColCount + 1

Loop

'Update the person's name column with the explanation

Cells(Row, LastCol + 1).Value = StringStore & ");"

Row = Row + 1

Eye

MsgBox ("Successfully Done")

Preposterous Sub