Solved

Generate SQL script from Excel

Posted on 2001-07-19
4
338 Views
Last Modified: 2008-03-17
I'm comfortable with VBScript and ADO in ASP but have not worked with VBA in Excel.  What I'd like to do is generate an SQL script from an Excel document.  Each row in the document contains all of the parameters needed for each SQL statement.

Dim string_Column1, string_Column2
Dim array_OfRemainingColumnsThatActuallyContainData

'' BEGIN OUTPUT FOR EACH ROW

UPDATE table
SET Column1=string_Column1, Column2=string_Column2
WHERE UniqueID IN (array_OfRemainingColumnsThatActuallyContainData)
GO

Move.Next
0
Comment
Question by:ptpovo
4 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
Comment Utility
ptpovo, here is an Excel VBA example which takes one sheet and generates the SQL statements in the second sheet.

It will probably need some modification for your particular environment but you should be able to figure it out.

Public Sub CreateSQL()
    Dim lngRow As Long
    Dim lngCol As Long
    Dim strRemaining As String
    lngRow = 1
    With Sheets(1)
        Do While .Cells(lngRow, 1).Value <> ""
            lngCol = 3
            strRemaining = ""
            Do While .Cells(lngRow, lngCol).Value <> ""
                strRemaining = strRemaining & .Cells(lngRow, lngCol).Value & ", "
                lngCol = lngCol + 1
            Loop
            strRemaining = Left(strRemaining, Len(strRemaining) - 2)
            Sheets(2).Cells(lngRow, 1).Value = "UPDATE MyTable Set Column1 = '" & .Cells(lngRow, 1) & "', Column2 = '" & .Cells(lngRow, 2) & "'" & _
                " WHERE UniqueID IN (" & strRemaining & ")"
            lngRow = lngRow + 1
        Loop
    End With
End Sub
0
 

Author Comment

by:ptpovo
Comment Utility
Sorry I've let this one sit for awhile, is this a Macro or a Module?  How do I execute it?
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi ptpovo,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept TimCottee's comment(s) as an answer.

ptpovo, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now