Solved: Add data to a closed workbook

[phendrena]

Hi,

I'm extremely new to VBA and I am looking for some advice and pointers on how best to do what I want.

I currently have a workbook setup that uses a Uder Form which submits data to worksheet within the same workbook. The workbook itself has now become rather large and as such I was wondering if it is possible to have the User Form in workbook A and have it add the data to Workbook B instead of a seperate sheet in workbook A.

Ideally (and much into the future) I would like to setup links between excel and access.

For now though would anyone please be able to suggest some ideas and possibly sample code that I could study/use/adapt.

I've attached a copy of my workbook for you to view.

Many thanks

[fb7894]

Sounds like you're Workbook B is acting as a database. Maybe you'd have better luck if the UserForm in Workbook A updated an Access db?

[phendrena]

Sounds like you're Workbook B is acting as a database. Maybe you'd have better luck if the UserForm in Workbook A updated an Access db?

Ideally that is something that I would like to do. At the moment we are only using workbook A so it has the User Form and the database on a seperate worksheet. My skills with excel and access, don't currently, strech to linking between the two applications but this is something that I am work on.

[CreganTur]

Which UserForm writes to which worksheet?

[phendrena]

Which UserForm writes to which worksheet?

frmCanxData to CanxData & frmIVRData to IVRData

[CreganTur]

The only way I know to do this involves opening the workbook, inserting your data, and then closing it again.

I'd suggest setting the value of a variable as the filepath to your 'database' workbook:
[VBA]Dim wb As Workbook
Set wb = workbooks.open "FilePath"[/VBA]

Then you target the cell where you want to insert your userform data by declaring the workbook->worksheet->range you want to work with. Something like:
[VBA]wb.Sheet1.Range("A2").Value = Me.txtBox[/VBA]
The above code will put the value of the UserForm object named txtBox into cell A2 of worksheet 1 of the workbook you chose earlier.

Then you can save and close 'wb'

[Kenneth Hobs]

Nice layout and colors in your workbook.

I would recommend creating an Access database. ADO methods could then easily append records as needed. If you would post an MDB with a table for the Sheet CanxData, we could show you how it is done. It is probably easier to do it like that than some other methods for 2 workbooks.

[phendrena]

Nice layout and colors in your workbook.

I would recommend creating an Access database. ADO methods could then easily append records as needed. If you would post an MDB with a table for the Sheet CanxData, we could show you how it is done. It is probably easier to do it like that than some other methods for 2 workbooks.

Thank you for the comments re layout/colours

I do want to move to using an access database and once i've granted access i'll sort one out and post again.

[phendrena]

Nice layout and colors in your workbook.

I would recommend creating an Access database. ADO methods could then easily append records as needed. If you would post an MDB with a table for the Sheet CanxData, we could show you how it is done. It is probably easier to do it like that than some other methods for 2 workbooks.

Hi,

I've finally been granted access to MS Access
I've now setup a brief and basic database.

There are two user forms in the excel spreadsheet : frmCanxData and frmIVRData.
There are two tables in the Access Database : CanxData and IVRData

How do I get the data from excel into Access avoiding data being overwritten (the excel user forms will eventually be loaded as an add-in on the command bar, as such multiple users can update simultaneously).
(I'm using Office '97 at work btw).

xls & mdb files attached in the zip

Thanks,

[Kenneth Hobs]

Here is one method. Notice how I put the fieldnames in row1. You can easily add your Excel range and the MDB and table names.
[VBA]Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub[/VBA]

[phendrena]

Thanks Kenneth,

I will admit that looking at the code as it flew over my head was fun (my vba knowledge isn't that good tbh).

Would you be able to explain that code a little more please and how it works taking the data from the userform (frmCanxData) and putting into Access and not putting the data onto the worksheet?
(I want to get rid of the worksheets for data storage and jst use Access).

Thanks,

[Kenneth Hobs]

I forgot that you were using a form method. Form methods by nature do not lend themseleves to this method though it can be done. One simply adds the fieldnames to some Sheet that could be hidden, and in row 2, one references the cells from the "field" sheet. You can name a range or just use a bracket method to set the range.

You can make forms in Access and skip Excel alltogether.

If you do want to stick with Excel, we will have to use ADO methods. A more robust method can be used to skip the intermdiate step that I explained in the first paragraph. It will be more hard to understand but once you understand the concept, it is not that bad. Both of these ADO methods will work.

If you want to purse one of the 2 ADO methods, let me know which one. I can help you with a more specific code for your xls and mdb.

[phendrena]

Hi Kenneth,

We don't really want the end user to have access MS Access, we would prefer them to use excel if at all possible. I'm more than happy to go with the harder option as it would be good to learn, but if you want to provide examples of both methods that would be great.

Of course, once I get the Excel communicating with Access, i'll no doubt be posting in the Access forum as, well, Access is alien to me tbh!

Thanks,

[CreganTur]

We don't really want the end user to have access MS Access, we would prefer them to use excel if at all possible.

I hope I'm not butting in, but I wanted to point out that you could create an Access front-end specifically for your users to input their data. It would consist of linked tables connected to your backend data tables. You could build it so it only contains the forms and methods that are absoutely required for the User to do their job.

Through the built-in startup options you could keep the User from ever seeing the database window (As long as you disable the ability to shift-open a database) and display the Form you want them to see.

This would simplify a lot of the issues you're currently running into.

Just my two cents

[Kenneth Hobs]

When you learn more Access, you can do what CreganTur said. With the effort spent in Excel, you could do it in Access just as well.

This ADO method is fairly simple. I left many comments in it to help you do something else if needed. Once you copy this, delete out the more extensive comments as needed.

The first step is to click the Tools menu in the VBE and set the reference to Microsoft ActiveX Data Objects 2.8 Library. This is the ADO reference.

Use this code to replace and add parts to your Update button code for frmIVRData. Some parts, I did not change at all. Double click your Update button for the userform and paste over your other data. Do this on a backup copy of both your xls and mdb files.

Some things that I added, changed and suggest.
1. Took the "Me." out of the click event. It knows which object it is using. It doesn't hurt anything of course.
2. Took the code out to reset control values. Unload Me will clean up for you.
3. Added the ADO routine ADOIVRdata. I will explain this after these points.
4. Changed the Initialization routine to show how you can use the List property rather than a loop to additems to a combobox or listbox. Nothing wrong with doing it your way though.

Some notes about your database in general.
1. Try to adopt a consistent naming convention for your MDB table fieldnames.
a. I use an underscore rather than a space character for fieldnames. For one thing, it is very easy to add more than one space character but think that you used one. This is the case with fieldname "Dealer or Policy Number". If you delete the extra space in the table, do it in this Excel code as well.
b. Do not use characters like "?" in fieldnames. This could cause confusion.
c. Do not use command or function names like Date or Time for fieldnames.

Some notes about the ADO routine.
1. There is only one place that you need to tweak to get it to work providing that you added the ADO reference as explained earlier.
a. Search for "***". Ctrl+F in the VBE is what I do. Set your MDB path and name. The 2nd "***" is the SQL with the IVRData. You will need to change the 2nd part for the other table.
2. The call to the ADO routine will error if your MDB is open by someone. So, it might be better to put the call to ADOIVRdata before adding the data to your worksheet in the Click event.
3. We finally get to the good part. Search for the comment "Adding a new record". It should be fairly obvious how I set the Access field values based on the Date, Time and values from the userform. I gave two examples. Notice how I used the most current Date and Time as values rather than strings from the userform.

If I haven't lost you:
[vba]Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("IVRData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, "A").Value = txtDate.Value
ws.Cells(iRow, 2).Value = txtTime.Value
ws.Cells(iRow, 3).Value = cboTLName.Value
ws.Cells(iRow, 4).Value = txtCMName.Value
ws.Cells(iRow, 5).Value = cboWhoCalled.Value
ws.Cells(iRow, 6).Value = cboScheme.Value
ws.Cells(iRow, 8).Value = txtPolNo.Value
ws.Cells(iRow, 9).Value = CboReason.Value
ws.Cells(iRow, 10).Value = txtComments.Value

ADOIVRdata 'Append the form's data to the Access database

Unload Me
End Sub

Private Sub ADOIVRdata()
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer, Row As Integer, s As String

On Error GoTo ErrorHandler
' Database information - *** Set MDB path and name here
DBFullName = ThisWorkbook.Path & "\Saves Tool.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
' Next lines critical to work in QPro properly.
' It does hurt to use them in Excel though.
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
' Filter - *** And sets table name
Src = "SELECT * FROM IVRData "
'Src = Src & "and CategoryID = 30"
Recordset.Open Source:=Src, ActiveConnection:=Connection
' Cells.Clear 'Used in Excel to clear a sheet
' Write the field names
'For Col = 0 To .Fields.Count - 1
'Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name 'Excel method
'Next
'If .RecordCount < 1 Then GoTo EndNow 'Query found no matching records
' Write the recordset by Excel method
'Range("A1").Offset(1, 0).CopyFromRecordset Recordset
'Add a new record (not pushed to the database until Update)
'MsgBox CStr(.RecordCount), vbInformation, "#Records"

' Adding a new record
.AddNew
.Fields("Date") = Date 'Use most current date
Recordset("Time") = Time 'Use the most current time
Recordset("Team Leader").Value = cboTLName.Value
Recordset("Customer Manager") = txtCMName.Value
Recordset("Who Called?") = cboWhoCalled.Value '? is in the fieldname
Recordset("Scheme").Value = cboScheme.Value
Recordset("Dealer or Policy Number") = txtPolNo.Value 'Two spaces in fieldname
Recordset("Reason for Call") = CboReason.Value
Recordset("Comments") = txtComments.Value

'Recordset("ShipCity") = Worksheets("City").Range("C3")
' Push the new record to the Access Database. Until now, the data was disconnected.
.Update
' MsgBox CStr(.RecordCount), vbInformation, "#Records"

End With
ErrorExit:
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
Exit Sub

ErrorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume ErrorExit
End Sub

Private Sub UserForm_Initialize()
Dim cTL As Range
Dim cWC As Range
Dim cReason As Range
Dim cScheme As Range
Dim ws As Worksheet
Set ws = Worksheets("Lookups")
'For Each cTL In ws.Range("TeamLeaders")
' With Me.cboTLName
' .AddItem cTL.Value
' .List(.ListCount - 1, 1) = cTL.Offset(0, 1).Value
' End With
'Next cTL
cboTLName.List = WorksheetFunction.Transpose(ws.Range("TeamLeaders"))

For Each cWC In ws.Range("WhoCalled")
With Me.cboWhoCalled
.AddItem cWC.Value
.List(.ListCount - 1, 1) = cWC.Offset(0, 1).Value
End With
Next cWC
For Each cReason In ws.Range("IVRReasons")
With Me.CboReason
.AddItem cReason.Value
.List(.ListCount - 1, 1) = cReason.Offset(0, 1).Value
End With
Next cReason
For Each cScheme In ws.Range("Scheme")
With Me.cboScheme
.AddItem cScheme.Value
.List(.ListCount - 1, 1) = cScheme.Offset(0, 1).Value
End With
Next cScheme
Me.txtDate.Value = Format(Date, "dd/mm/yy")
Me.txtTime.Value = Format(Time, "hh:mm")
Me.txtCMName.Value = Application.UserName
Me.cboTLName.Text = vTLName
Me.txtCMName.SetFocus
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use Close Button!"
End If
End Sub
[/vba]

[phendrena]

Hi Kenneth,

Many thanks for the detailed advice, i have a question...

The first step is to click the Tools menu in the VBE and set the reference to Microsoft ActiveX Data Objects 2.8 Library. This is the ADO reference.

The version of this that i can see with excel '97 etc is 2.7, is this going to make a massive difference to the above code as i've tried using it, and nothing gets passed to Access (directly copied and pasted - although i have changed the field names in the vba and also the database to include '_' and removed the '?').

Thanks,

EDIT : It did copy the data, when i originally created the database it included a lot of blank rows that i didn't delete, so i forgot to look at the end of the database....

[phendrena]

I hope I'm not butting in, but I wanted to point out that you could create an Access front-end specifically for your users to input their data. It would consist of linked tables connected to your backend data tables. You could build it so it only contains the forms and methods that are absoutely required for the User to do their job.

Through the built-in startup options you could keep the User from ever seeing the database window (As long as you disable the ability to shift-open a database) and display the Form you want them to see.

This would simplify a lot of the issues you're currently running into.

Just my two cents

Hi,

I'll certantly give this a whirl when i've gotten used to using Access, for the moment i'm happy to use Excel with ADO. but look out for me posting in the Access forum soon though as i'll have some interesting queries.....

[Kenneth Hobs]

The only difference it would probably make is if others run the code and don't have that file. You can always download the latest version from Microsoft. However, try what you have is my suggestion since most of your users are probably using what you are.

[phendrena]

The only difference it would probably make is if others run the code and don't have that file. You can always download the latest version from Microsoft. However, try what you have is my suggestion since most of your users are probably using what you are.

I have indeed tried it and it does work, i'm just in the process of changing some sections of the code and adding the code to work with the CanxData form. You've shown me some interesting things in the code, especially with some of the tweaks you made to mine...
( cboTLName.List = WorksheetFunction.Transpose(ws.Range("TeamLeaders")) ).

It gets interesting now as i now need to get data from Access and into an Excel userform... i've posted in the access forum about this one. I'll mark this thread as solved now, as you've helped me so much with this one.

thanks Kenneth

[Kenneth Hobs]

Glad it worked out for you.

You can of course use a similar method to get data from Access. I have used this method to get data from a closed xls, MDB tables, and such to fill userform listboxes. Filling a textbox or other controls like this is easy too. You can filter the recordset by an sql string more than getting all records with, Select *. AddNew and Update would not be needed for that scenario.