transferring spreadsheet (as-is) from excel to an access form

[Guest]

I am redesigning an Excel Spreadsheet that is used to create service
contracts. The pages of the contracts are printed from each worksheet (each
worksheet is designed to look just like the form. Is there a way to export
these worksheet into Access Forms so that I can actually build a client
database around the 'contracts'?

I am no beginner in Access, but am not well-versed in VB or VBA (though I do
understand the basic concept of it). Creating the table(s) won't be a
problem, nor will the queries or reports. I know I can create the forms from
scratch and will if I absolutely have to, but it would make my work SO much
easier if it were possible to just import the 'forms' from Excel into Access
and just insert fields for data.

Any help on this would be greatly appreciated.

 

[M.L. Sco Scofield]

Sorry Steve. I'm aware of no product that will do this. There have been lots
of requests for converters over the years. Usually people want to go from
Access to Visual Basic where they at least 70% or 80% similar.

Coming from an un-structured thing like a spreadsheet would either be very
specific to the one sheet you are converting or be a *lot* of work to make
something generic. And although I'm sure it would be possible to write a
bunch of code to do this, you will be able to recreate everything much
faster and still have some time left over for a cup of tea.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Communications Director www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Jim Carlock]

Steve,

You can create the input on the spreadsheet, then in the VBA
code on the spreadsheet, send the data directly to a table inside
of Access. You'll need to do it via ADO or via DAO. I have
done it in the past using DAO, where you'd go into the VBA
editor and click on the References to add a reference to DAO
and then use:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRS as String
Dim sDB As String
Dim iCount As Long

sDB = Application.Path & "\mdbname.mdb"
sRS = "tTableName"

Set db = DAO.OpenDatabase(sDB)
Set rs = db.OpenRecordset(sRS)
rs.MoveLast: rs.MoveFirst
iCount = rs.RecordCount

Dim sName As String
'could set up a for/next loop or a while Not rs.EOF loop
sName = rs.Fields("Name").Value

'...
'code to kill recordset and database objects
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

ADO will be very similar, except you'd be using:

Dim oADODB As ADODB.Connection
Dim oADODBRS As ADODB.Recordset

Hope that helps.

--
Jim Carlock
Post replies to newsgroup.

I am redesigning an Excel Spreadsheet that is used to create service
contracts. The pages of the contracts are printed from each worksheet (each
worksheet is designed to look just like the form. Is there a way to export
these worksheet into Access Forms so that I can actually build a client
database around the 'contracts'?

I am no beginner in Access, but am not well-versed in VB or VBA (though I do
understand the basic concept of it). Creating the table(s) won't be a
problem, nor will the queries or reports. I know I can create the forms from
scratch and will if I absolutely have to, but it would make my work SO much
easier if it were possible to just import the 'forms' from Excel into Access
and just insert fields for data.

Any help on this would be greatly appreciated.