July 8, 2005 at 9:25 am
Steve,
thanks. This is a good approach. I use Excel and Access VBA sometimes. I can just suggest another simple way.
You can click Tools->Macro->Record New Macro. Give the new macro the name FillInsert for example and click OK. Then right away Tools->Macro->Stop recording. Then Tools->Visual Basic Editor. You will see a template for a new procedure in the right pane if you click under Modules -> Module 1 on the left. Just enter 3 lines of For...Next block in between the procedure name and End Sub. Save. Back in Excel Do Tools->Macro->Macros. Clcik on the one that you just created and click Run. Watch the sells filling up with your statement.
Sub FillInsert()
For i = 1 To 25
Cells(i, 3).Value = "insert productmap select " & Cells(i, 1) & "," & Cells(i, 2)
Next i
End Sub
Please, note that
-you have to replace 25 with your actual number of records
- the long line filling up the cells should be all in one line
- You can write code in VB Editor without creating Macro template first, just do it manually, I used it as example for those who are not comfortable with code
- Module 1 could be actually Module 2 or Module 25 based what is already in your Excel application
- This code will work in the active worksheet. To specify a worksheet you have to qualify it exactly like in SQL Server. Something like that:
Worksheets("sheet1").Range("C5:C10").Cells(1, 1).Value
Yelena
Regards,Yelena Varsha
July 8, 2005 at 9:47 am
Excellent out-of-the-box use of Excel!
I was on an Access97 -> SQL2000 migration last year, and had to do a similar hack. In my case, I used some SQL script to programmatically build my inserts and updates, and pasted the generated statements from Query Analyzer back over to my huge migration batch.
I was pretty pleased with myself, but this Excel technique would have been faster!
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum."
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply