0

I am writing a program that allows a user to search for an image using an ID. It finds the image and saves it to the users desktop. All of this works perfect. The next item that I want to add is metadata. On another tab of the program, I want the program to access an excel file that will house all of the photo metadata, such as ID, Description and Source. To get this metadata, I need the program to take the ID the user provides, search for it on the excel and then from the adjacent cells next to that search term, pull the information and put it into the correct textboxes. For example, User puts in iStock ID 62365645, the program searches the excel for that ID (Which is in Column A), locates it and then grabs the Source (Which is in column B) and then the description )which is in column C). The display that information in the correct textboxes: Description = txtDescripton Source = txtSource

I am having trouble figuring out the correct code to pull that information from the adjacent cells and display it in the text fields. Example of Program:

enter image description here

Example of Excel: enter image description here

Code:

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

    Dim xl As New Excel.Application '= New Excel.Application
    Dim iStockMetadata As Excel.Workbook = xl.Workbooks.Open("Z:\04 - CPE\iStock Photo Database\iStock_Metadata.xlsx")
    Dim sheet As Excel.Worksheet

    sheet = iStockMetadata.Sheets(1)

    'Photo Database folder path
    Dim sourceFolderPath = "Z:\04 - CPE\iStock Photo Database" ' Folderpath without spaces
    Dim sourceFolderPath2 = "Z:\04 - CPE\iStock Photo Database" ' Folderpath without spaces
    Dim sourceFolderPath3 = "Z:\04 - CPE\iStock Photo Database" ' Folderpath without spaces
    Dim sourceFolderPath4 = "Z:\04 - CPE\iStock Photo Database" ' Folderpath without spaces
    Dim sourceFolderPath5 = "Z:\04 - CPE\iStock Photo Database" ' Folderpath without spaces

    ' iStock Number reference ID input
    Dim fileNumber = txtSearch.Text
    Dim fileNumber2 = txtSearch2.Text
    Dim fileNumber3 = txtSearch3.Text
    Dim fileNumber4 = txtSearch4.Text
    Dim fileNumber5 = txtSearch5.Text

    'Taking the user input for the iStock ID and searching the source folder for it, with catches in case the image is missing
    Try
        ' For image 1
        Dim sourceFilePath = Directory.GetFiles(sourceFolderPath, $"*{fileNumber}.jpg").FirstOrDefault()
        If Not IsNothing(sourceFilePath) Then
            Dim destinationFolderPath = My.Computer.FileSystem.SpecialDirectories.Desktop
            Dim sourceFileName = Path.GetFileName(sourceFilePath)
            Dim destinationFilePath = Path.Combine(destinationFolderPath, sourceFileName)
            File.Copy(sourceFilePath, destinationFilePath, True)

            Me.txtID.Text = ""
            txtID.Text = txtSearch.Text

            Dim SourceID1 = txtSearch.Text
            Me.txtDescription.Text = ""
            txtDescription.Text = iStockMetadata.cells(SourceID1, 1, 500000000)

            txtResult.Text = "Image found and saved successfully"
        Else
            txtResult.Text = "Either no input or Image not found"
        End If

' Other images Code goes here

    Catch
        ' If user is not connected to the VPN, a message will display informing user to connect
        MsgBox("You need to be connected to the VPN to access the Photo Database Folder, Please connect to VPN and try again", MsgBoxStyle.OkOnly, "VPN Not Connected")
    End Try
    xl.ActiveWorkbook.Close(False)
    xl.Quit()
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Renae Shore
  • 17
  • 4
  • I only program VBA but your error seems to be a logical one in that you retrieve a text here `txtDescription.Text = iStockMetadata.Cells(SourceID1, 1, 500000000)`. Instead you should retrieve the location of that text, extract the row number from the location, copy the entire row to an array, and then display elements of that array in your form's text boxes. – Variatus Sep 26 '20 at 07:14
  • 3
    Whoa.. You launch a new copy of Excel to open a sheet every time the user clicks the search button, and then hope that it shuts down and goes away by the next time the user clicks the search button?! Perhaps you should [read the excel file into a datatable](https://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable) and just keep the one datatable and serach that (or bind your controls to the datatable via a bindingnavigator and use the Filter property then all this becomes automatic .. – Caius Jard Sep 26 '20 at 07:19
  • These `Dim sourceFolderPathXXX= "Z:\04 - CPE\iStock Photo Database" ' Folderpath without spaces` repeated lines are redundant (they always contain the same string) and seem to disgree with the comment (the path name contains spaces despite the comment saying it doesnt) – Caius Jard Sep 26 '20 at 07:20
  • Why don't you write a program to move the Excel data to a proper database. Excel is not a database and it is awkward to try to use it as one. Once your data is properly stored. It will be very easy to search it and update it. – Mary Sep 26 '20 at 23:17

0 Answers0