is it POSSIBLE to create a Database in excel using VBA...

[Senthil]

Hello friends,

I have unique requirement, I'm looking to understand the possibility to
create a database using VBA in excel where the user can uplad attachments as
well. Below are the actions/formats required.

1) Create a form for the users enter the data.
2) Multiple user should be able to use the form and the information should
get updated in a master workbook/sheet.
3)Users should be able to upload an attachment using the form as well.
4)need to create a search and sorting (user, file # wise...etc) criteria so
that the data can be retrived easily for future reference and audit.
5) If a row is selected in the database and clicked - it should show all the
attachment realated to it. So that ppl can retrive the attachment as well.
6) Should be able to run reporting to analyze the data as well.

Constrains - This is for team of 10 users, unfortuantely we don't have
financial aid nor required skills to create a tool in SQL or DotNet or
MS-ACCESS. We want to keep it simple so that anyone in the team can improvise
it later. The main constrain being to upload attachment and retrive it.

This database will be created in shared network, so that all the users can
access it.

Hope i didn't confuse it, however pls do drop an email if you have questions
and suggestions. I have very tight dead line to implement a data tracker (its
my idea to add the attachment as well so that we have all the relevent doc in
one place)..

Thanks in advance!
Senthil

 

[Sammy]

Hello friends,

I have unique requirement, I'm looking to understand the possibility to
create a database using VBA in excel where the user can uplad attachments
as
well. Below are the actions/formats required.

1) Create a form for the users enter the data.
2) Multiple user should be able to use the form and the information should
get updated in a master workbook/sheet.
3)Users should be able to upload an attachment using the form as well.
4)need to create a search and sorting (user, file # wise...etc) criteria
so
that the data can be retrived easily for future reference and audit.
5) If a row is selected in the database and clicked - it should show all
the
attachment realated to it. So that ppl can retrive the attachment as well.
6) Should be able to run reporting to analyze the data as well.

Constrains - This is for team of 10 users, unfortuantely we don't have
financial aid nor required skills to create a tool in SQL or DotNet or
MS-ACCESS. We want to keep it simple so that anyone in the team can
improvise
it later. The main constrain being to upload attachment and retrive it.

This database will be created in shared network, so that all the users can
access it.

Hope i didn't confuse it, however pls do drop an email if you have
questions
and suggestions. I have very tight dead line to implement a data tracker
(its
my idea to add the attachment as well so that we have all the relevent doc
in
one place)..

Thanks in advance!
Senthil

Yes Excel can be used to act as a DataBase! However, as far as attachments,
it will depend what you want to attach, and how the Data is to be used.

As for using as a DataBase, a simple Compare sub is needed, the sub below is
extended to show you an example of how to compare elements opposed to the
complete value in a cell.

--
Public Sub BasicDataBaseSub()
Dim GetDate, GetDay, GetMonth, GetYear

GetDate = TextBox1.Text ' TextBox1 contains the Text you wish to compare
GetDay = Day(GetDate)
GetMonth = Month(GetDate)
GetYear = Year(GetDate)

Sheets("SHEETNAME").Select ' Add the Worksheet name you wish to run the sub
on

For a = 1 To 100 ' Change to the required Cell Range ie A50:A149 change to
For a = 50 to 149

CheckDay: ' Change sub to whatever you wish. Be aware that you may need to
alter the name to ensure you don't clash with pre-defined sub names
b = Day(Cells(a, 2)) ' Cells(a, #) where # is the Column number ie B=2 C=3
D=4 etc
If b = GetDay Then GoTo CheckMonth
GoTo Skip ' If more than 1 compare, add a 'Skip' sub to bypass code between
success when comparing Cells and what you require as a result

CheckMonth:
c = Month(Cells(a, 2))
If c = GetMonth Then GoTo CheckYear
GoTo Skip

CheckYear:
d = Year(Cells(a, 2))
If d = GetYear Then GoTo Success

Skip:
Next a
GoTo Exit1 ' Use this when comparison fails in all Cells

Success:
' Use this area to apply code required when comparison is 100% successful

Exit1:
End Sub
--

 

[royUK]

You can create a simple database in Excel. All you really need is
table set out with a header row. You should have no empty rows o
columns between data.

Excel even has an inbuilt Data Form. Select a cell in your table an
from the Data menu select Form.

Not sure what you wan to attach. If it is another workbook or documen
then have a column in which you can store the path & file name, mayb
even as a hyperlink.

If you want to crate your own form there is an example here

'VBA Project  Protection' (http://www.excel-it.com/vba_examples.htm

 

[joel]

I wouiuld recommend using Access as the database if there are multiple users
rather than excel. You can create the database and retrieve data from Excel
and Excel VBA.

I set up two macros for somebody earlier this year where the Excel VBA
Create the database and uploaded the data. I then set up a query for the
person so that the data automatically downloaded everytime the workbook was
opened. it was also setup so that multiple people could simultaneously
access the data.

Below are 3 macros
1) Create database
2) Upload data to database
3) Create a query that will update everytime the workbook gets opened. this
macro only need to get run once.

Public Const Folder = "C:\Temp\"
Public Const FName = "submission.mdb"
Sub MakeDataBase()

Const DB_Text As Long = 10
Const FldLen As Integer = 40


strDB = Folder & FName

If Dir(strDB) <> "" Then
MsgBox ("Database Exists - Exit Macro : " & strDB)
Exit Sub
End If

' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True


' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
' Create new table.
Set tdf = dbs.CreateTableDef("Submissions")

' Create Task/ID field in new table.
Set fld = tdf. _
CreateField("Task_ID", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Client Name field in new table.
Set fld = tdf. _
CreateField("Client Name", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Effective Date field in new table.
Set fld = tdf. _
CreateField("Effective Date", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Imp Mgr field in new table.
Set fld = tdf. _
CreateField("Imp Mgr", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Due Date field in new table.
Set fld = tdf. _
CreateField("Due Date", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Actual Date field in new table.
Set fld = tdf. _
CreateField("Actual Date", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Date Difference field in new table.
Set fld = tdf. _
CreateField("Date Difference", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

dbs.TableDefs.Append tdf

Set appAccess = Nothing


End Sub

Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

strDB = Folder & FName

If Dir(strDB) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strDB)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="Submissions", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF <> True Then
.MoveLast
End If
End With

With Sheets("Internal Project Plan")

ClientName = .Range("B4")
ImpMgr = .Range("B5")
LaunchDate = .Range("C4")

LastRow = .Range("K" & Rows.Count).End(xlUp).Row
For RowCount = 7 To LastRow

If UCase(.Range("K" & RowCount)) = "X" Then

DueDate = .Range("E" & RowCount)
ActualDate = .Range("F" & RowCount)
DateDif = .Range("M" & RowCount)
Accurate = .Range("L" & RowCount)
Task_ID = .Range("B" & RowCount)

With rs
.AddNew
!Task_ID = Task_ID
![Client Name] = ClientName
![Effective Date] = LaunchDate
![Imp Mgr] = ImpMgr
![Due Date] = DueDate
![Actual Date] = ActualDate
![Date Difference] = DateDif

.Update
End With
End If
Next RowCount

End With

Set appAccess = Nothing
End Sub

Sub CreateQuery()
'
' Macro4 Macro
' Macro recorded 1/19/2009 by Joel
'
strDB = Folder & "\" & FName
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=" & strDB & ";" & _
"DefaultDir=" & Folder & ";" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5"), _
Array(";")), Destination:=Range("A1"))

.CommandText = Array( _
"SELECT Submissions.Task_ID," & _
"Submissions.`Client Name`," & _
"Submissions.`Effective Date`," & _
"Submissions.`Imp Mgr`," & _
"Submissions.`Due Date`," & _
"Submissions.`Actual Date`," & _
"Submissions.`Date Difference`" & _
Chr(13) & "" & Chr(10) & _
"FROM `C:\temp\submission`.Submissions Submissions")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub