Need to convert formatted-free xls sheet to database table

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

crakthin

Thread Starter
Joined
Jun 29, 2004
Messages
4
Hi there,

I am trying to convert my xls file into a database table. However, the problem is the xls file wasn't formatted in the traditional database style which has all field names at the first row and data rows after.

I wonder if there are any add-ins that can do this task. If not, do I need to write vba to handle it? I have like a thousands of sheet like the attached one and at this moment, not quite sure how to do.

Please help.
CR
 

Attachments

Joined
Jun 23, 2004
Messages
12
Do you have 'Access'? If so, import the Excel file into Access to create a database. Microsoft has an Add-In you can download at no cost. It is Excel 2002 'Template Wizard with Data Tracking'. Hope this works for you.
 

crakthin

Thread Starter
Joined
Jun 29, 2004
Messages
4
Hi Thimblina,

A ton of thanks for your help to push me out of this hectic. The template wizard seems to be the one I am looking for. However, I wonder for yr access advice. Not sure how to proceed because my excel sheet is not formatted the way the database is. That way how should i do to combine access with template wizard or what?

Thanks,
CR
 

crakthin

Thread Starter
Joined
Jun 29, 2004
Messages
4
Some more questions

My previous sheet has whatsocalled "subtable" within it. You can see that it said "1st event sponsored" ... "4th event sponsored"... That way how should I proceed to use template wizard to handle it? To me it seems template wizard can't directly handle subtable. Am i right?

Thanks,
CR
 
Joined
Sep 4, 2003
Messages
4,912
I think using VBA would be your best bet. I can help you with the code if you can tell me what fields your database has and what cell values need to be added to the database. Do all the spreadsheets follow the exact same format?


Rollin
 

crakthin

Thread Starter
Joined
Jun 29, 2004
Messages
4
Hi Rollin,

Thanks a lot for your generosity. However, I would like to try my best first on this work. If I'm really in need, I will tell you immediately.

Cheers,
CR

PS. FYI, the sheet has bold cells as database fields and other cells on the right of fields are data value. Also, all sheets have the same format.
 
Joined
Sep 4, 2003
Messages
4,912
Here's a little code to get you started. Good luck!


Sub InsertRecord()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\YourDataBaseName.mdb;" 'Change to your Database Path
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "YourDataBaseTableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A1").value
.Fields("FieldName2") = Range("A2").value
.Update ' stores the new record
End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top