Database creation

[DebbyG]

Hello

I have a large spredsheet consisting of 3,500 rows and approx 50 columns from which I need to make a database.

I need to have a "form template" which I can merge the spreadsheet data into and print each individual record (which is each individual row with 50 fields) separately.

Ideally I need this to look like an access database, but am not sure if this can be done in excel.

I don't know if I've explained myself clearly.

Any help is greatly appreciated. ray:

Thank you.

 

[BrianB]

1. Excel will go up to 65,536 rows - so no problem.
2. Say your data sheet is Sheet2. To refer to cell A1 from another sheet you need the formula (in R1C1 style)
=Sheet2!R1C1.
3. The aim is to now make the row/column numbers variable to get a copyable formula.
In another sheet (your form)
a. In cell A1 put the number 1 - this will be the column number. You can hide column A later.
b. Select any other cell (say C1) and give it the Range Name 'RowNumber' and enter 1 into the cell you have chosen.
c. In cell B2 put the formula :-
=INDIRECT(CONCATENATE("Sheet2!","R",RowNumber,"C",$A1),FALSE)
d. You can now copy the formula in B2 down and enter column numbers in column A.
e. Changing the RowNumber cell will show corresponding data
4. Connect the code below to 2 buttons on your template called NEXT and PREVIOUS to automate the RowNumber change.

'---------------------------------------------------------------------------
Sub NEXT_ROW()
    ActiveSheet.Range("RowNo").Value = ActiveSheet.Range("RowNo").Value + 1
End Sub

'---------------------------------------------------------------------------
Sub PREV_ROW()
    If ActiveSheet.Range("RowNo").Value > 2 Then
        ActiveSheet.Range("RowNo").Value = ActiveSheet.Range("RowNo").Value - 1
    Else
        MsgBox ("Top of list.")
    End If
End Sub
'---------------------------------------------------------------------------

 

[DebbyG]

Thank you BrianB, I appreciate your response.

I will give this a try.

 

[Joe Was]

You may want to try the built-in Excel Data Form for managing an Excel database. On the pull-down menu it is Data-Form...

The default form will update a database, search, querry and all sorts of things automatically. To set it up label a block of columns with your field names put the cursor in the first record(row) and activate the utility.

The code below will work with a table that is on the same sheet as the navagation menu. I use buttons and Photo objects that act as buttons to activate the code below. For your data table I start in AA1 with cells labled as text and lable the fields (AA1,AB1,AC1...)then load the table with at least one data element in AA2 (A dummy like: "First Record" or 1, anything will work.) this sets the table for the data-form defalts. The Data-Form will use your column lables as the forms field lables. It all works very slick. The code below uses the Sendkeys command to activate or use the Data-Form.

Note Data forms can display a maximum of 32 fields at one time. You can only have one Data-List per sheet, but you may have many sheets in a workbook. JSW

Sub DBForm()
'Macro made; 12/13/2001, By: Joe Was.
'Keyboard Shortcut: Ctrl+b.
'Runs the DataBase input Form.

'Calibrate screen for scroll to DataBase-Data-Table.
Sheets("Menu").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=13

'Select first DataBase record.
Range("AA2").Select

'Clean Screen and show Default Data Form.
ActiveWindow.ScrollColumn = 1
SendKeys "%do"

End Sub


Sub myExit()
'Exit the database form.
SendKeys "%fx"
End Sub


Sub myClose()
'Close the datbase form.
SendKeys "%fc:"
End Sub


Sub myPrint1()
'Print the data table.
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Print Data Now?" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Ready to Print?" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Yes.
Columns("AA:AI").Select
ActiveSheet.PageSetup.PrintArea = "$AA:$AI"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollColumn = 1
Else ' User chose No.
GoTo Can
End If
End
Can:

End Sub

Sub mySave()
'Save this workbook.
ActiveWorkbook.Save
End Sub

Sub VDBT()
'Move to the data table.
Range("AA2").Select
End Sub

Sub MainView()
'Return to the navagation menu.
ActiveWindow.ScrollColumn = 1
End Sub