Online Excel Database

Im looking for a solution which i need to achieve using excel.
Im creating a VBA application using excel.
I need all the data on the excel sheet to be stored online in some table/database.
This would enable me to use the same excel sheet on multiple systems at the same time and having up to date information on all the systems.
Please let me know whether such a thing is possible.
Also let me know the various options available.

You may consider saving the data as a CSV file to a shared drive. The following is code to read and write CSV Files. However, I recommend using MS-Query to read the data from the CSV file. It will bring it in as a table. Also it will preserve the data type. Sometimes when you import a big string that looks like a number, Excel converts it to scientific notation which is not what you generally want.

Option Explicit

' Syntax: ReadCSV CSVPath, CSVFile, PasteSheet, PasteRange
' Syntax: WriteCSV CSVPath, CSVFile, CopySheet, CopyRange


Sub ReadCSV(CSVPath As String, CSVFile As String, PasteSheet As String, PasteRange As String)
Dim xlBook As Excel.Workbook, xlCSV As Excel.Workbook

Set xlBook = ThisWorkbook

Application.DisplayAlerts = False

' Clear the old data
Sheets(PasteSheet).Cells.ClearContents

' Open the CSV File
If Dir(CSVPath & "\" & CSVFile) = "" Then
    MsgBox "File " & CSVFile & " does not exist"
    Exit Sub
End If

Set xlCSV = Workbooks.Open(CSVPath & "\" & CSVFile)
Cells.Copy

' Paste to CSV
xlBook.Activate
Sheets(PasteSheet).Select
Range(PasteRange).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

' Close the book
xlCSV.Close savechanges:=False
Application.DisplayAlerts = True

End Sub


Sub WriteCSV(CSVPath as String, CSVFile as String, CopySheet as String, CopyRange as String)
Dim xlCSV As Excel.Workbook, xlBook As Excel.Workbook

Application.DisplayAlerts = False
Set xlBook = ThisWorkbook
Sheets(CopySheet).Select

' Create the CSV File
Set xlCSV = Workbooks.Add(xlWBATWorksheet)

' Copy the data
xlBook.ActiveSheet.Range(CopyRange).Copy _
    xlCSV.Sheets(1).Range("A1")
    
' Save and close the CSV File
xlCSV.SaveAs Filename:=CSVPath & "\" & CSVFile, FileFormat:=xlCSVMSDOS
xlCSV.Close savechanges:=True
Application.DisplayAlerts = True

End Sub

I'll put in a plug for Excel files here. Excel files know how big they are so you always know what the copy range is.

Here is a pointer to how to work with Excel files: http://www.excelforum.com/excel-prog...-database.html.

Here is code on how to clear an Excel file. The syntax is ClearTable SheetName, TableName

Option Explicit

Sub ClearTable(TableSheet As String, TableName As String)

If FilterIsOn(Sheets(TableSheet).ListObjects(TableName)) = True Then
    Range(TableName).AutoFilter
    Range(TableName).AutoFilter
Else
    Range(TableName).AutoFilter
End If

If Range(TableName).Rows.Count > 2 Then
    Range(TableName).Delete
    Exit Sub
End If

If CountFields(Range(TableName & "[#Headers]").Offset(1, 0)) > 0 Then
    Range(TableName).Delete
End If

End Sub

Function CountFields(MyRange As Range) As Long
Dim cl As Range
Dim Counter As Long

On Error Resume Next
Counter = 0
For Each cl In MyRange
    If Len(cl.Value) > 0 Then
        Counter = Counter + 1
    End If
Next

CountFields = Counter

End Function

Function FilterIsOn(lo As ListObject) As Boolean
 
Dim bOn As Boolean
 
bOn = False
 On Error Resume Next
 If lo.AutoFilter.Filters.Count > 0 Then
     If Err.Number = 0 Then bOn = True
 End If
 On Error GoTo 0
 FilterIsOn = bOn
 End Function