PDA

View Full Version : VBA, Excel, VBScript, SqlServer



brd123
11-03-2006, 06:50 PM
I imported the contents of a CREATE table statement from SQL Server into Excel--all data is in one column. I would like to see a VBA example of how to move through the rows and parse the results into columns--the "Text to Columns" menu function is inadequate. I would like a VBA macro to parse the name, data type, null constraints into seperate columns.: (see below)

[VariantID] [int] IDENTITY(1,1) NOT NULL,
[VariantGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ProductVariant_VariantGUID] DEFAULT (newid()),
[IsDefault] [int] NOT NULL CONSTRAINT [DF_ProductVariant_IsDefault] DEFAULT ((0)),

So, the rows will be parsed like this:

ColA Colb Colc Cold
------------- ------------------ ------------- ---------
[VariantID] [int] IDENTITY(1,1) NOT NULL

ColE
--------------------------------
CONSTRAINT ...

I am thinking the object vbscript.regexp would do the trick.

10west
11-03-2006, 10:18 PM
Put the column, into an array of it's rows, c1r1, c1r2, actually, just do this
For C = 1 To 1
For R = 1 To Rcnt 'you may have to set this val, or detect it
excelSheet.CELLS(C, R).Value = excelSheet.CELLS(R, C).Value
'makes columnXX, equal rowXX

Next
Next

10west
11-03-2006, 10:23 PM
Private Sub excelRowtoColumn()
Dim Excel As Object
Dim excelSheet As Object
Dim application As Object
Dim C As Integer
Dim R As Integer
''On Error Resume Next
Set Excel = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set Excel = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not load Excel.", vbExclamation
End
End If
End If

Set excelSheet = Excel.ActiveWorkbook.Sheets("Sheet1")
Rcnt = 253 'set rows or detect
Ccnt = 1
For C = 1 To Ccnt
For R = 1 To Rcnt
excelSheet.CELLS(C, R).Value = excelSheet.CELLS(R, C).Value
Next
Next

Exit Sub
End Sub

10west
11-03-2006, 10:33 PM
If you need, you could parse each rows value, and spit it across the columns. You use the split command, and enter your delimiter, the dimension of the array, will be the distance each row will spit into the columns, so the C value, would take this value through the loop at each row. Just offset the first column to 2, or a non 1, as the new data will not overwrite, yet, the first value in the cell, is common to both. Looks like I misunderstood on the first pass. Just split the rows, it looks like, by spaces " ". Then increment the column value, for each array member, and place the value in that col, from that cell, on down the sheet.

10west
11-03-2006, 10:55 PM
Rcnt = 25 'set rows or detect
Ccnt = 1
For C = 1 To Ccnt
For R = 1 To Rcnt
ExcPieces = Split(excelSheet.CELLS(R, C).Value, " ") 'put in your delimiter, this is a space i'm assuming
For i = 0 To UBound(ExcPieces)
excelSheet.CELLS(R, i + 2).Value = ExcPieces(i)
Next i
Next
Next
This chunks the first cell, a number of columns over, starting with the first adjacent cell, to the cell being split.

10west
11-03-2006, 10:57 PM
you'll have to trim that "," off the end, or replace it with nothing, and deal with the spaces in the text, that you don't want to split, like a pre-parse

mdmackillop
11-04-2006, 05:28 AM
Hi 10West,
Welcome to VBAX,
A couple of tips, when you're posting code, if you select it and click the VBA button it will be formatteed as Post 3 above, making it more readable.
Use line breaks in long lines of code or it scrolls of the screen, which is a pain for those of us with small screens.
Regards
MD

brd123
11-04-2006, 08:58 AM
Hi 10West,
Welcome to VBAX,
A couple of tips, when you're posting code, if you select it and click the VBA button it will be formatteed as Post 3 above, making it more readable.
Use line breaks in long lines of code or it scrolls of the screen, which is a pain for those of us with small screens.
Regards
MD

brd123
11-04-2006, 09:20 AM
Thanks for replying. I have attached the text file of what I am trying to parse. Using the space character does not always work as a delimitter. I am also attaching the excel workbook.

I need a way to decide where each elment (field name, data type etc...) of each row needs to be placed (i.e., in which column). I was looking at the vbscript.regexp expression as a possibility.

thanks again

acw
11-06-2006, 06:12 PM
Hi

Try running the following code across the text in a spreadsheet, then use text to columns with a delimiter of ~. It won't put it into the right columns, but I think it is close to keeping the relevant blocks together.

If the parsing is giving you the right "blocks", you could use code to work through each column and look at the first word in the cell. If it does not belong in that column, then move it across to the correct output column.


Sub ccc()
Cells.Replace what:="] [", replacement:="]~["
Cells.Replace what:=" DEFAULT", replacement:="~DEFAULT"
Cells.Replace what:=" CONSTRAINT", replacement:="~CONSTRAINT"
Cells.Replace what:=" COLLATE", replacement:="~COLLATE"
Cells.Replace what:="] NULL", replacement:="]~NULL"
Cells.Replace what:="] NOT NULL", replacement:="]~NOT NULL"
Cells.Replace what:=" IDENTITY", replacement:="~IDENTITY"
End Sub



Tony

brd123
11-07-2006, 09:47 AM
I received an answer that solved my design problem from a site called helpshare.com. Based on this one experience, I would recommend it. Downside is that you have to buy the answer--but not a lot of money. I got an answer that I could work with in about a day and it cost me $25. You get to set the price however.