Thread: VBA Data Entry Form - Populating Separate Book

Hello All,

I have built a data entry form (Input tab) and have code populating a table (Table tab). Currently both are in the same book, but I would like to house them in separately.

What is the proper VBA syntax for this? Will both books need to be open in order to populate the table?

The 'real' data is hosted on sharepoint and is getting darn big. The result is problems opening the doc and looong save times. My thought was to have the secretaries doing data entry open a single sheet (Input Form) which will then populate the data in another sheet. I'm dreaming, aren't I...

I know Access would solve everything, but for cost reasons I am stuck with Excel.

Any help, suggestions, insults, etc will be greatly appreciated.

-chiz

Both orkbooks would need to be open, but you will encounter problems if two users are writing to the database at the same time.

With your existing code the NewRow variable should be declared as a Long. Try this instead

Code:

Private Sub CommandButton1_Click()
    Dim NewRow As Long

    With Worksheets("table")
        NewRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NewRow, 1).Value = Worksheets("Input").Range("B1").Value
        .Cells(NewRow, 2).Value = Worksheets("Input").Range("B2").Value
        .Cells(NewRow, 3).Value = Worksheets("Input").Range("B3").Value
        .Cells(NewRow, 4).Value = Worksheets("Input").Range("B4").Value
        MsgBox "New Data added", vbOKOnly, "Notice"
    End With

    Worksheets("Input").Range("B1:B4").ClearContents
End Sub

maybe you could have code to export the table contents to master workbook at the end of the day

It doesn't really matter, a sheet form still achieves the same result and users may be more comfortable with it.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

This code will prompt the user to select the workbook containing the data to import. It then determines what data to copy - whether to include header rows. The data is then imported to the master workbook.

You need to check the specifiedsheets are correct for your use.
You might want to add code to clear the data from the source workbook.

Code:

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : click here for more examples and Excel Consulting
' Purpose   : Import data fro selected workbook to master workbook
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit


Sub Get_Value_From_All()
    Dim wbSource As Workbook
    Dim wbThis As Workbook
    Dim rToCopy As Range
    Dim uRng   As Range
    Dim rNextCl As Range
    Dim bHeaders As Boolean
    Dim iFilterIndex As Integer
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False

        On Error Resume Next

        Set wbThis = ThisWorkbook
        'chck for headers
        Set uRng = wbThis.Worksheets(1).UsedRange
        If uRng.Cells.Count <= 1 Then
            'no data in master sheet
            bHeaders = False
        End If
        ' Get the filename
        '  ' Get the filename
        sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)
        Workbooks.Open Filename:=sWb
        Set wbSource = ActiveWorkbook
        Set rToCopy = wbSource.Worksheets(1).UsedRange
        With wbThis.Worksheets(1)
            Set rNextCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            If bHeaders Then
                'headers exist so don't copy
                rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
                                            rToCopy.Columns.Count).Copy rNextCl
                'no headers so copy
                'place headers in Row 2
            Else: rToCopy.Copy .Cells(1, 1)
                bHeaders = True
            End If
        End With
        wbSource.Close False                 'close source workbook
        On Error GoTo 0
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    Set wbSource = Nothing
    Set wbThis = Nothing
    Set uRng = Nothing
    Set rToCopy = Nothing
    Set rNextCl = Nothing
End Sub

HI,

My suggest is in excel 2003 some database format cannot read it and its very difficult to open this type of document,that time the the doc type in a seperate to any other document...

Thank u...

Originally Posted by peter12

HI,

My suggest is in excel 2003 some database format cannot read it and its very difficult to open this type of document,that time the the doc type in a seperate to any other document...

Thank u...

I haven't a clue what you mean