Convert Excel sheet to Database?

J

Jim Clark

I have an Excel spreadsheet that someone tried to create
to track boxes of paper records that are being sent out
for imaging. The spreadsheet looks something like this:
Box 1 Box 2 Box 3...
123 234 435
124 236 467
126 242 487
137 267 578
etc.
Being that we've already boxed 200+ boxes with 400 to go,
we will soon run out of the 250 columns under this setup.

How can I convert this file to Access where the Column
Header remains constant for each row item under it... i.e.
Box Num Record Num
1 123
1 124
1 126
....
2 234
2 236 etc.
Can you help?
 
O

onedaywhen

You are probably just looking for the SQL queries, but just for fun
here's the whole thing (you need to change some constants e.g. to
point at your Excel workbook, worksheet, etc):

Sub Test()

Dim Cat As Object
Dim Con As Object
Dim strConJet As String
Dim strConXL As String
Dim strSql1 As String
Dim strSql As String
Dim strSqlCounter As String
Dim lngCounter As Long

' Amend the following constants to suit
Const PATH As String = "" & _
"C:\Temp\"
Const FILENAME_JET As String = "" & _
"MyNewJetDB.mdb"
Const FILENAME_XL As String = "" & _
"MyExisitngWorkbook.xls"
Const TABLE_NAME_JET As String = "" & _
"MyNewJetTable"
Const TABLE_NAME_XL As String = "" & _
"MyExistingExcelSheet$"
Const COLUMNS_XL As Long = 3 ' << 600+ !!

' Do NOT amend the following constants
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>"
Const CONN_STRING_XL As String = "" & _
"[Excel 8.0;" & _
"Database=<PATH><FILENAME>;].[<TABLE_NAME>]"

' Build connection strings
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "<PATH>", PATH)
strConJet = Replace(strConJet, "<FILENAME>", FILENAME_JET)

strConXL = CONN_STRING_XL
strConXL = Replace(strConXL, "<PATH>", PATH)
strConXL = Replace(strConXL, "<FILENAME>", FILENAME_XL)
strConXL = Replace(strConXL, "<TABLE_NAME>", TABLE_NAME_XL)

' Build generic sql statements
strSql1 = ""
strSql1 = strSql1 & " SELECT 1 AS [Box Num],"
strSql1 = strSql1 & " [Box 1] AS [Record Num]"
strSql1 = strSql1 & " INTO " & TABLE_NAME_JET
strSql1 = strSql1 & " FROM " & strConXL

strSql = ""
strSql = strSql & "INSERT INTO " & TABLE_NAME_JET
strSql = strSql & " ([Box Num], [Record Num])"
strSql = strSql & " SELECT <COUNTER> AS [Box Num],"
strSql = strSql & " [Box <COUNTER>] AS [Record Num]"
strSql = strSql & " FROM " & strConXL

' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.Create strConJet

' Run SQL against new Jet database to
' create new table and data
Set Con = Cat.ActiveConnection
Set Cat = Nothing
With Con

' First XL column creates the new table
.Execute strSql1

' Use generic sql for subsequent XL columns
For lngCounter = 2 To COLUMNS_XL
strSqlCounter = Replace(strSql, "<COUNTER>", CStr(lngCounter))
.Execute strSqlCounter
Next

.Close
End With
End Sub

--
 
Top