Import from Excel - Matrix

[DugsDMan]

I'm looking for a way to import data from Excel. The problem is that the data is layed out in a true matrix style. Basically, I have programs in the "1" row and layouts in the "A" column. When a certain program reads a layout, there will be an "X" in the corresponding cell. For example, if Program K reads Layout 7, then there would be an "X" in the K7 cell. What I am trying to do is get a two column table out of this data. I would like to show which programs read which layouts in a table format, like below:

Program Layout
A 2
A 14
B 7
B 23
etc, etc, etc

The table I have goes from cell A1 to cell GU763 (That's 763 x 203). So, as you can tell, trying to manually enter all this data would drive me insane.

I thought about writing a VB app that would read in the file, put the layouts/program names into an array, then just spit out the values, but didn't know if there was a better/easier/quicker/whatever way to do this.

Any ideas is greatly appreciated!!!

Thanks,
Doug

 

[rdroske]

You could create a new Macro in the Excel file and Under Tools/References check AxctiveX Data Objects.

Then something like the following code is pretty much all it would take to do it. I'm not totally clear on what you have in your Ecel file but this would loop through the rows
1-763 and check each column 1-203 for an X and add row to the table when it found one. You'll have to adjust some.


Sub MacroName()
Dim Con As New ADODB.Connection
Dim Cmd As New ADODB.Command

Con.ConnectionString = "driver=SQL SERVER; server=INFOCON;UID=username;pwd=password;database=DBName"
Con.Open
Cmd.ActiveConnection = Con
For I = 1 To 763
For J = 1 To 203
If ActiveSheet.Cells(I,J) = 'X' Then
Cmd.CommandText = "INSERT INTO TableName VALUES (" + STR(J) + "," + STR(I) + &quot"
Cmd.Execute
End If
Next J
Next I
Con.Close

End Sub