Update Excel records using Access

[Jamel]

I currently link multiple excel spreadsheet with an access database file because I often run update queries to update data in throughout my spreadsheets. This allows me to run update queries based on multiple conditions verse doing this with a long vlookup formula.
Also by doing it this way I can preserve formulas in the spreadsheet that would be lost if I did a full import and export of the spreadsheet.
In 2002 Access discontinue this functionality so I've be stuck using Access 97.
Does anyone have a workaround that allows me update date based on other spreadsheets data.

for example If one spreadsheet has data that includes name address social #account number( which is 4 parts). And the other spreadsheet has Social #and Job # and account number 4 parts.

And I want to update the first spreadsheet to include Job # based on where social # and acct number matches. How can I do this in excel.

Its easy to do in access!

Help

 

[Fazza]

If I'm understanding correctly, Jamel, you want to update one Excel file from another.

I think the mention of Access is just because that is what you currently use.

Here are some late bound examples - so no references required. Here is some code placed in workbook (I've named first) to update a table in that file from separate file named "second.xls". My common field name is account, and the job field is being updated. [second.xls was closed, the code in first.xls]

Sub update_in_this_file_from_another()
 
  Dim strSQL As String
  Dim strConn As String
  Dim objRS As Object
 
  strSQL = "UPDATE [Sheet1$] A INNER JOIN `c:\second.xls`.[Sheet1$] B ON A.account = B.account SET A.job = B.job"
 
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""
 
  Set objRS = CreateObject("ADODB.Recordset")
  objRS.Open strSQL, strConn
  Set objRS = Nothing
End Sub

Here is some very similar code, placed somewhere else. It updates first.xls with job data from second.xls. Again late bound. This code can be in MS Access, or Excel or Word or Outlook or Power Point, AFAIK.

Sub update_first_file_from_second_file()
 
 'this code just about anywhere
  Dim strSQL As String
  Dim strConn As String
  Dim objRS As Object
 
  strSQL = "UPDATE `c:\first.xls`.[Sheet1$] A INNER JOIN `c:\second.xls`.[Sheet1$] B ON A.account = B.account SET A.job = B.job"
 
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\first.xls;Extended Properties=""Excel 8.0;"""
 
  Set objRS = CreateObject("ADODB.Recordset")
  objRS.Open strSQL, strConn
  Set objRS = Nothing
 
End Sub

I just tried this later code from MS Outlook and first.xls was updated OK from data in second.xls. Both Excel files were closed. [In fact, I guess you don't need Excel installed on the machine, just the files. A little bit like, btw, you can use mdb files without having MS Access installed, but that is for another thread/time.]

The connection strings are for pre Excel 2007.

OK?

PS As you can see, this is (seemingly) easy & the code succinct. I've (self) learnt this sort of stuff from the internet. I encourage you to do likewise, it will take a little time but it does offer the 'riches' you envisage. You can do some really powerful stuff with a little SQL and ADO, and VBA. cheers, F

 

[Fazza]

How was that, Jamel?

 

[Jamel]

I never understood how to apply your fix. sorry. I got frustrated
when I couldn't figure it out.

 

[Fazza]

Seems like you're ready to try again.

How are things going (generally)? Enjoying working with data? Ready for new steps?

 

[Jamel]

Yes, I am ready to start. I just don't know where to begin. Also I'm extremely intimidated by all this. Code scares me! I need help. More like a boost!
Where do I begin with all this? I'm so frustrated because I know this would boost my career. I'm so tired of running from this!!!

 

[Fazza]

Yes, I am ready to start. I just don't know where to begin. Also I'm extremely intimidated by all this. Code scares me! I need help. More like a boost!
Where do I begin with all this? I'm so frustrated because I know this would boost my career. I'm so tired of running from this!!!

Just start. Anywhere. And keep going, Jamel.

It will be slow - and maybe frustrating - initially. Just keep going.

Perhaps a book from http://www.alibris.com/booksearch?mtype=B&keyword=walkenbach

One of the Power Programming with VBA from a few years ago is cheap. I still refer to my Excel 2000 version.

Study the book. Code the examples. Use the macro recorder. Search the internet when you need more help. Maybe ask on the forum.

Enjoy the journey. Keep learning & growing. You can do it.