Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Generate SQL script from Excel

Posted on 2001-07-19
4
341 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
ID: 6302154
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
ID: 6343554
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
ID: 7156229
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
ID: 7182273
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

856 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