Look if Access field data available in the Excel file using VBA

[ImruDa]

Hello everyone!

Iam new to Access programing. I want to write a vba code in Access that looks if field data (available in access user interface) is also available in the excel spreedsheet and then copy the data available on the next right cell of exel to table in the MS Access.

Anyone can share me the relevent code or help me howw to achieve this? Thank you

 

[theDBguy]

Hi. If you link to the Excel file, you can use DLookup() to get the data.

 

[ImruDa]

Hi. I have attached the files as an example like in access db I have a table and there are 6 codes written in there and I have to look in a excel file in Column B if these codes are available there and then copy the corresponding right cell i.e. from column C and paste it in the right of the code written in the Access db table.

So far I have written following code to select an excel file where codes info available and I dont know what to wite further.. Iam getting error 3001 that object sheet1$ not found

Dim diag As Office.FileDialog Dim item As Variant Dim db As DAO.Database Set db = CurrentDb Set diag = Application.FileDialog(msoFileDialogFilePicker) diag.AllowMultiSelect = False diag.Title = "Please select an Excel Spreadsheet" diag.Filters.Clear diag.Filters.Add "Excel Spreadsheets", "*.csv, *.xlsx" If diag.Show Then For Each item In diag.SelectedItems txtfilename = item Next End If DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "Tabelle2", "txtfilename", True[COLOR=rgb(250, 197, 28)], "Sheet1$" [/COLOR] '<-- Sheet name CurrentDb.TableDefs.Refresh

please help me!

 

[theDBguy]

Hi. I have attached the files as an example like in access db I have a table and there are 6 codes written in there and I have to look in a excel file in Column B if these codes are available there and then copy the corresponding right cell i.e. from column C and paste it in the right of the code written in the Access db table.

So far I have written following code to select an excel file where codes info available and I dont know what to wite further.. Iam getting error 3001 that object sheet1$ not found

Dim diag As Office.FileDialog Dim item As Variant Dim db As DAO.Database Set db = CurrentDb Set diag = Application.FileDialog(msoFileDialogFilePicker) diag.AllowMultiSelect = False diag.Title = "Please select an Excel Spreadsheet" diag.Filters.Clear diag.Filters.Add "Excel Spreadsheets", "*.csv, *.xlsx" If diag.Show Then For Each item In diag.SelectedItems txtfilename = item Next End If DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "Tabelle2", "txtfilename", True[COLOR=rgb(250, 197, 28)], "Sheet1$" [/COLOR] '<-- Sheet name CurrentDb.TableDefs.Refresh

please help me!

Hi. Did you try using DLookup()?

 

[ImruDa]

Hi. Did you try using DLookup()?

My excel file will change every time with different name so I want to write a code so that I every time I select the right file. I don't want to use manuell linking with excel file.
I think I can only use dlookup when I have data available in access dB I don't know with this function I can lookup in an external excel file data.

 

[theDBguy]

My excel file will change every time with different name so I want to write a code so that I every time I select the right file. I don't want to use manuell linking with excel file.
I think I can only use dlookup when I have data available in access dB I don't know with this function I can lookup in an external excel file data.

Hi. Please explain what exactly you mean by "different." Since we are not familiar with your situation, we can't guess what may or may not work for you. For example, you said you want to "look up" a value in Excel, how do you know which column it is in? Does it always have the same header name?

 

[ImruDa]

Hi. Different means every time I will be using different excel file to look up for a similar value which is available in my access dB table. That's why I want to write a vba which opens a dialog box and ask me to select the excel file that have the value details as I shown in the picture. The matching values will always be in the column B of excel file. There will be several values in this column so I only look for those which are also in my table of Access dB. Thank you for your question

 

[theDBguy]

Hi. Different means every time I will be using different excel file to look up for a similar value which is available in my access dB table. That's why I want to write a vba which opens a dialog box and ask me to select the excel file that have the value details as I shown in the picture. The matching values will always be in the column B of excel file. There will be several values in this column so I only look for those which are also in my table of Access dB. Thank you for your question

Okay, no issue then. You can use your file browser code to have the user select the new file, link to it, then use DLookup(), since the data you want to look up will be in the same column anyway. Does the code you posted above work to link the selected Excel file? If not, what is actually happening? If you're getting an error message, what does it say?

 

[ImruDa]

The above code just opens the dialog box and when I select it to go in the excel data I am getting an error 1003 on the second last line of my code (I think it is not getting my excel sheet name object) . And I also don't how to write the Dlookup function further

 

[ImruDa]

Anyone? Please☹

 

[theDBguy]

Anyone? Please☹

Hi. Sorry for the delay. Been busy with something else and still can't go back to this right now. I'll check again later. Cheers!

 

[Gasman]

Where did you get that code from?

Try removing [COLOR] code and [/COLOR] from the statement.

An easy way out of this would be to rename the old excel workbook to something else and then rename new workbook to required name.

In fact try
[code[
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "Tabelle2", "txtfilename", True, "Sheet1$" '<-- Sheet
[/code]

I really hope you changed the values to match your DB

 

[ImruDa]

Ok thanks now I can link access db to the chosen excel file. Now I have to move further and have to use Dlookup function() in the linked excel file to search if the data (available on field1 of table1 in my access db--- see file attached) is availble in the column 2 or now Field 2( as now excel linked to access db). After checking it is availabe then copy the right next cell from field 3 and paste in the corresponding right next cell in the Table1 of access db ( the table from we took the data to lookup in the excel). Thank you

 

[theDBguy]

Ok thanks now I can link access db to the chosen excel file. Now I have to move further and have to use Dlookup function() in the linked excel file to search if the data (available on field1 of table1 in my access db--- see file attached) is availble in the column 2 or now Field 2( as now excel linked to access db). After checking it is availabe then copy the right next cell from field 3 and paste in the corresponding right next cell in the Table1 of access db ( the table from we took the data to lookup in the excel). Thank you

Hi. I don't see an attachment, but here's a link to the DLookup() function.

Application.DLookup method (Access)

Office VBA reference topic

docs.microsoft.com

 

[ImruDa]

Hi. I don't see an attachment, but here's a link to the DLookup() function.

Application.DLookup method (Access)

Office VBA reference topic

docs.microsoft.com

Hi. I have attached file with linked excel file. Now I want to use Dlookup() function in the linked excel file to search if the data (available on field1 of tabelle1 in my access db--- see file attached) is availble in the Feld1( excel linked to access db). After checking it is availabe then copy the right next cell from feld2 and paste in the corresponding right next cell in the Tabelle1 of access db ( the table from we took the data to lookup in the excel).