Search, Update & Insert a row in another workbook Table

[inddon]

Hello there,

Based on the below thread from Belleke and other sources I have put together a workbook which would search a Table row from another workbook and display in the active workbook. This is not based on a UserForm, and is based on a worksheet in File 2.xlsm.

The below solution from is based on a UserForm and a single workbook:
https://chandoo.org/forum/threads/h...to-an-excel-table-and-back.39072/#post-234169

In my case, there are 2 files having student marks:
File 1.xlsx - Contains Master Data
File 2.xlsm - Search, Update and Insert linked to the above File 1.xlsx

File 2, is the Search, Update and Insert tool.

Unique Identifier Table Column/Cell: Student ID/FSearchStudentID

Search:
The User enters the Student ID and on Search button click, it retrieves the Table row from File 1.xlsx and displays the values on File 2.xlxm

The functionality of Update and Insert, I am not able to get it working.

Update:
In case of button Update: The User search and display the details on File 2. He updates the cells and on button click, it should update the relevant Table row in File 1.xlsx

Insert:
In case of button Insert: The User enters new details on the cells in File 2 and on button click Insert it should check in File 1, if the row exists, if not then insert a new row in the start of the table as first row.

Opening of File 1.xlsx:
I am using AD for this functionality. One problem I come across, is the Master file 'File 1.xlsx' opens in a read-only mode. Is it possible to open in write-mode, or without opening the file do all the operation?


I request if someone could please review my code and help me out in getting the Insert and Update to work.

Attached sample workbooks for your reference.

Thanks & regards,
Don

 

[Luke M]

Hello again, Don.

As you've discovered, there's a few parts to this problem. If we try to do thing with two different files, there's the issue of getting info to and from File 1. While you can read fairly easy from a closed file, I'm not aware of ways to modify the contents (w/o some serious XML manipulation skills). I don't suppose we can change the process so that the data and search tool are in a single workbook?

Other idea would be to have the data in File 1 being stored in an actual database, like Access or SQL.

Failing those 2 options, we'd have to look at opening File 1 in the background while doing stuff with File 2. Is that a viable solution?

 

[inddon]

Hello Luke,

Thanks for having a look at the files and proposing possible options.

Given the circumstances of the people who are going to use it, I would prefer the last option of keeping 'File 1' open in the background (eg. hidden).

For the Search functionality (in 'File 2' - Sub GetStudentData), I have used assigning like 'File 2' (RangeName) = 'File 1' Table[Column Name].

If such an approach can be adopted for all (Search, Update & Insert) would be wonderful. In future, this will be of help to others in understanding applying the changes accordingly.

Many thanks again & regards,
Don

 

[Luke M]

Hi Don,

Here's my changes to the file, which seems to have all the capabilities now that you want. I didn't make any changes to the sheet module code. In the General Module, I just commented out some of the MsgBox items. In Procedures, several edits to handle opening the File1, updating values, etc. Have a read through and let me know if you have questions on anything?

 

[inddon]

Hi Luke,

Thank you for the update.

Actually, I couldn't get forward in making the following functionality to work and therefore need your help. This is for:
1. Update -Sub UpdateStudentData()
2. Insert -Sub InsertStudentData()

The Search functionality is working as expected.

I mentioned in my original post the actual requirement. However, to get more clarity and insight I have attached a word document describing the functionality and technical requirement more clearly. Also, a minor addition included.

I have modified your File 2.xlsm to include the minor change of adding the Student ID and removed the debug messages.

Many thanks for your help.

Look forward to hearing from you.

Regards,
Don

 

[Luke M]

Well, drat. Looks like I did a bunch of work and didn't save it. That's no good.

Take a look at this one, which really does have an Update and Insert macro fleshed out.

 

[inddon]

Oops sorry to hear that. Thank you for getting it done again

In the mean time, I checked, in one of your file where you had given a Table related solution to me (long time back). Extracted some table related contents from it and worked (amateur) out an Update and Insert. Just finished it (attached file)

Will check your draft version and let you know.

Regards,
Don

 

[inddon]

Well, drat. Looks like I did a bunch of work and didn't save it. That's no good.

Take a look at this one, which really does have an Update and Insert macro fleshed out.

No doubt, your draft version is much better than what I did

I am glad it is finally getting into shape and moving towards the way it should work. Many thanks again.


Some minor things I encountered:
1. In all the 3 sub's the below code is giving an error, even though the workbook 'File 1' is open.

wb.Close savechanges:=True

Changes:

2. Is it possible to keep the ADO connection Open, and close it when closing 'File 1'?

3. Whenever the User opens 'File 2', the program can open 'File 1' in the background, and hide it. When the User exit 'File 2', then save 'File 1' without any prompts. Can this be added?

4. in the Sub Insert:
In my sample Draft version 'File 2 Don Draft.xlsm', I wrote the code to get the max of student id from the table (File 1).

After the record is inserted in the table, the max(student id) is reflected in 'File 1' in RangeName: FStudentID. Can this be done in a smart way?

In the 'File 2' RangeName: FStudentID, can this be made not enterable as it's value would be auto populated as above?

5. Whenever the User 'Inserts' or 'Updates', at the end of the operation, the program should autosave 'File 1' without any prompts.


I have updated the file with some comments:
Sub Update:
1. Replaced EntryWS.Range("FSearch StudentID") with EntryWS.Range("FStudentID")

Sub Insert:
1. Comments about auto populate and reflect of FStudentID
2. Removed the check fields should be entered. Now it only checks for Student Name and Marks not empty

Attached updated 'File 2 Luke Draft2.xlsm' for your reference.


Regards,
Don

 

[Luke M]

Hi Don. Hope you had a good weekend.

1. Not sure what's causing your error, as it's working ok on my end. Can you confirm that wherever File 1 is located you/user has write access? I tweaked code slightly so that the Workbook.Open calls this out, but may be something that needs more investigation.

2. I'm not currently using ADO to write to File 1, so I'm not sure why we would open and close it. You can certainly write that in if you want, but you'd probably want to figure out how to use the ADO to write to File 1 so that it's worth something.

3. So, File 1 would then function almost like some extra sheets in File 2, eh?
Current process is that the file is only getting opened and closed when user clicks a button. This is the safer option, generally, as you are running a code and user can't interrupt the process. Can certainly try to use the Workbook_Open and Workbook_Close event macros. Would be something like this in the Workbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim strPath As String
  Dim wb As Workbook
  strPath = ThisWorkbook.Path & "\File 1.xlsx"
  If CheckFileIsOpen("File 1.xlsx") Then
    Workbooks("File 1.xlsx").Close savechanges:=True
  End If
End Sub
Private Sub Workbook_Open()
  Dim strPath As String
  Dim wb As Workbook
  strPath = ThisWorkbook.Path & "\File 1.xlsx"
  If Not CheckFileIsOpen("File 1.xlsx") Then
    Set wb = Workbooks.Open(Filename:=strPath, ReadOnly:=False)
  End If
End Sub

I've ammended the individual procedures to work with this.

4. I didn't see any new code for the Insert module on getting max value. Guessing that when you insert a record, you want to auto-assign that max value + 1, I've changed the code to suit.

5. I'm not seeing these changes. Perhaps you did the same as me?

 

[inddon]

Hi Luke,

Thank you for your reply. I had a wonderful and relaxing weekend Hope the same with you too.


For the button 'Search', I am using the ADO to get the details from 'File 1'
Your response on ADO has made me realize using ADO is not necessary. This can be done with the technique you showed in Update and Insert, using the Table.ColumnName feature.

I have learned something new from you . Will always remember this approach, thank you. The auto populate code of 'Student ID' looks good, will adapt the same in the workbook. Thanks again.

I will restructure the code and add the new changes from your draft 3 file in the sample workbook and get back to you.

--------------------------
One Last addition
--------------------------
I wanted to add a Button 'List of Values' which will display all the table rows from File 1 in a UserForm. In the Userform, there is search field 'Student Name'. When the User enter a character it will search through all the Names and display only those rows which satisfy the string value (something like google search)

The User selects the searched row. On click button OK, it assigns the values of the UserForm row to the worksheet range ields(Student ID, Student Name & Marks).

This would be the first time for me to see how this could be done using external workbook. I understood from some post that using a ListView might not be compatible with Excel 64 bit, therefore ListBox would be the right approach.

-------------------------------------------------------------------
Search Student Name: <Search TextBox> (Find Button)
-------------------------------------------------------------------

Sr Student Name Marks
--- ----------------- -------
10 ABC 25
9 PQR 15
...
...
...
-------------------------------------------------------------------
OK Cancel
-------------------------------------------------------------------

Attached updated workbook with the UserForm1 for your reference:
'File 2 Luke Draft3 .xlsm'


Many thanks & regards,
Don

 

[Luke M]

Don,

Give this one a look. You might want to play around with placement and format of column labels on the list box, but this should give you a place to start. Many ways of getting the info to the box...I went with filtering the source table, copying the visible cells to a hidden sheet, and then using that as the source.

I also made an adjustment to the Open/Close macros so that File1 now is hidden in the background.

 

[Luke M]

After I posted, I realize it would be safer to specify which data/columns to copy over. I also wanted to make sure the filter on File 1 gets cleared when done searching. See this version instead.

 

[inddon]

Hi Luke,

Thank you for the update. It works good. It is nice to see the File 1 is hidden

I tried to modify the sub md_Find_Click to display all the records, which it does. Disabled the line of code .Range.AutoFilter field:=


What I would like to have is the following:
(A)
-Display all the records in the Listbox.
-The User enters a string and clicks the button: Search
-Then it should display only those records (for the Search string entered) in the Listbox.
-The User clicks on a record
-On click OK, it copies the values to the sheet.

(B)
-Display all the records in the Listbox.
-The User clicks on a record
-On click OK, it copies the values to the sheet.

I tried to tweak this, in vain. Can this be done?

This will be the end of requirements.

Many thanks & regards,
Don

 

[Luke M]

I don't think I understood that bit about disabling the AutoFilter line. In the file I posted, it should already be able to do your options A and B.

Here, I typed the letter "b" and then hit search. I get these results:


If I select Zuber line and then hit Ok:

you can see that the tool has filled in that info. On the other hand, with UserForm open, if you leave the search field blank and hit Search, you'll get a list of all records.

 

[inddon]

Sorry Luke, my mistake . I didn't try click on Button Search with empty string.

It works perfect!.

Thanks a lot for taking the time to assist me with this requirement.

Wish you a pleasant day.

Regards,
Don