none
Using excel as a database RRS feed

  • Question

  • Hi everyone,

    I have one excel workbook(database.xlsx) which I am using for storing data. The mentioned workbook is stored in my Desktop.(C:\Users\Ed\Desktop.xlsx). I'm using another workbook(Record.xlsm) using which I can update my database(database.xlsx). I have some details in Record.xlsm workbook and I want to insert those records in Database.xlsx by clicking on a button.

    For example, I have the following details in Record.xlsm

     

    David 10
    Smith 20
    John 30
    Peter 40

    The first column implies the name and second column ID. Header is not present in Record.xlsm, Starting point of data is A1.

    In Database.xlsx workbook I have the Header for different records and the header of Name & ID are C5 & F5 respectively.

    I want once I click on the button from Record.xlsm if any name present in the Record.xlsm workbook finds a match in  C Column of database.xlsm the corresponding ID will be updated in F column. 

    Please let me know if I need to explain it a bit more.

    Thanks in advance!

    Wednesday, May 20, 2015 6:17 PM

Answers

  • Hi,

    When use UsedRange in a Workbook, it represents the range that you were using. While in Desktop.xlsx, since the ColumnA and ColumnB didn’t use, the UsedRange will start and count from ColumnC. So I make a change for the code, use the UsedRange to get the rowcount in the worksheet, get the cell value by cell row number and cell column number. And I have tested it , and you can down load it here.

    http://1drv.ms/1HNf6hI

    Private Sub CommandButton21_Click()
    Dim NameValue As String
    Workbooks.Open ("C:\Users\Ed\Desktop\Desktop.xlsx")
    RowCount1 = Workbooks("Record.xlsm").Worksheets("Sheet1").UsedRange.Rows.Count
    RowCount2 = Workbooks("Desktop.xlsx").Worksheets("Sheet1").UsedRange.Rows.Count
    For i = 1 To RowCount1
    Cell1value = Workbooks("Record.xlsm").Worksheets("Sheet1").Cells(i, 1).Value
    For J = 1 To RowCount2
    Cell2Value = Workbooks("Desktop.xlsx").Worksheets("Sheet1").Cells(J, 3).Value
    If Cell1value = Cell2Value Then
     Workbooks("Desktop.xlsx").Worksheets("Sheet1").Cells(J, 6).Value = Workbooks("Record.xlsm").Worksheets("Sheet1").Cells(i, 2).Value
    End If
    Next J
    Next i
    MsgBox "done"
    End Sub
    

    Best Regards,

    Lan

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Ed_Dao Thursday, May 28, 2015 5:17 PM
    Thursday, May 28, 2015 4:48 AM
    Moderator

All replies

  • Hi,

    >> I want once I click on the button from Record.xlsm if any name present in the Record.xlsm workbook finds a match in  C Column of database.xlsm the corresponding ID will be updated in F column

    Based on my understanding, you are going to find data in one workbook that matches another workbook, and fill the relevant data. You may go through each cell’s data in another workbook, get the rownumber and columnnumber ,and then fill other data. The following code you could use as a reference, it has supposed all the data in sheet1.

    Sub test()
    Dim NameValue As String
    For Each cell1 In Worksheets("Sheet1").UsedRange.Columns("A").Cells
    For Each cell2 In Workbooks("Desktop.xlsx").Worksheets("Sheet1").UsedRange.Columns("C").Cells
    NameValue = cell1.Value
        If NameValue = cell2.Value Then
        Rownumber1 = cell1.Row
    RowNumber2 = cell2.Row
       Workbooks("Desktop.xlsx").Worksheets("Sheet1").Cells(RowNumber2, 6).Value = Worksheets("Sheet1").Cells(Rownumber1, 2)
        End If
    Next cell2
    Next cell1
    MsgBox "done"
    End Sub

    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 21, 2015 7:41 AM
    Moderator
  • Hi Lanlan,

    Thanks for your reply.Desktop.xlsx file is present in my Desktop but still I’m getting Subscript Out Of Range error in the following line:

    For Each cell2 In Workbooks("Desktop.xlsx").Worksheets("Sheet1").UsedRange.Columns("C").Cells

    I have tried with the Full path of Desktop.xlsx but still no luck. 

    Could you please let me know what changes I need to make to get the desired result?

    Thanks.

    Thursday, May 21, 2015 8:40 AM
  • Hi Everyone,

    Can anyone please let me know how do I get rid of the above mentioned error!!

    Friday, May 22, 2015 1:15 PM
  • Does anyone have any update on this thread?
    Saturday, May 23, 2015 5:55 PM
  • Hi Ed_Dao,

    Sorry for the delay.

    >>Subscript Out Of Range error in the following line:

    This error often means that the object doesn't exist, since you have tried with the full path of Desktop.xlsx, I suppose that you didn't open Desktop.xlsx. you may open the Desktop.xlsx manually and run the code, or refer to the following code to open your file

    Workbooks.Open ("C:\Users\Ed\Desktop.xlsx")

    Hope this could help you

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Sunday, May 24, 2015 4:09 PM
    Moderator
  • Hi Lanlan,

    I have tried waht you mentioned but it's not working. Code is working fine this time without any error but Desktop.xlsx file is unable to update data from Record.xlsm file.

    Please note that Record.xlsm is a file in which I have some data and that data should be updated in Desktop.xlsx file which is actually my database file.

    I have uploaded both the files in Onedrive. Could you please check and let me know if I missed anything.

    https://onedrive.live.com/redir?resid=F2CDE6FFDEF5B21!107&authkey=!ALMt2Dm1RSsajBM&ithint=folder%2cxlsm




    • Edited by Ed_Dao Wednesday, May 27, 2015 5:30 AM
    Monday, May 25, 2015 3:49 PM
  • Hi Lanlan,

    Any update on this?

    Wednesday, May 27, 2015 5:30 AM
  • Hi,

    The code for Open WorkBook you should put it outside the loop. And when open the WorkBook using code, the ActiveWorkBook is changed, should add the workbook name in the code. I have modified the code, and the sample you can download it here.

    Sub database()
    Dim NameValue As String
    Workbooks.Open ("C:\Users\Ed\Desktop\Desktop.xlsx")
    For Each cell1 In Workbooks("Record.xlsm").Worksheets("Sheet1").UsedRange.Columns("A").Cells
    For Each cell2 In Workbooks("Desktop.xlsx").Worksheets("Sheet1").UsedRange.Columns("C").Cells
    NameValue = cell1.Value
    If NameValue = cell2.Value Then
    Rownumber1 = cell1.Row
    RowNumber2 = cell2.Row
    
       Workbooks("Desktop.xlsx").Worksheets("Sheet1").Cells(RowNumber2, 6).Value = Workbooks("Record.xlsm").Worksheets("Sheet1").Cells(Rownumber1, 2).Value
        End If
    Next cell2
    Next cell1
    MsgBox "done"
    
    End Sub
    

    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 27, 2015 7:46 AM
    Moderator
  • Hi lanlan,

    I have used your latest code but seems like still it’s not updating the ID field of Desktop.xlsx file. I have some sample name(A Column) in Record.xlsm file. If those names found a match with C Column of  Desktop.xlsx file corresponding ID will be updated from Record.xlsm file(B Column) to Desktop.xlsx file(F Column).

    I am using first row as header in Desktop.xlsx file but I don’t have any header in Record.xlsm file.

    Did it work in your system? Have you checked it? I’m uploading both the files once again and let me know if it is working in your system. I’m using excel 2007.

    https://onedrive.live.com/redir?resid=F2CDE6FFDEF5B21!116&authkey=!AM9ZkqK9UKgDWG8&ithint=folder%2cxlsx

    Thanks. 

    Wednesday, May 27, 2015 8:43 AM
  • Hi,

    When use UsedRange in a Workbook, it represents the range that you were using. While in Desktop.xlsx, since the ColumnA and ColumnB didn’t use, the UsedRange will start and count from ColumnC. So I make a change for the code, use the UsedRange to get the rowcount in the worksheet, get the cell value by cell row number and cell column number. And I have tested it , and you can down load it here.

    http://1drv.ms/1HNf6hI

    Private Sub CommandButton21_Click()
    Dim NameValue As String
    Workbooks.Open ("C:\Users\Ed\Desktop\Desktop.xlsx")
    RowCount1 = Workbooks("Record.xlsm").Worksheets("Sheet1").UsedRange.Rows.Count
    RowCount2 = Workbooks("Desktop.xlsx").Worksheets("Sheet1").UsedRange.Rows.Count
    For i = 1 To RowCount1
    Cell1value = Workbooks("Record.xlsm").Worksheets("Sheet1").Cells(i, 1).Value
    For J = 1 To RowCount2
    Cell2Value = Workbooks("Desktop.xlsx").Worksheets("Sheet1").Cells(J, 3).Value
    If Cell1value = Cell2Value Then
     Workbooks("Desktop.xlsx").Worksheets("Sheet1").Cells(J, 6).Value = Workbooks("Record.xlsm").Worksheets("Sheet1").Cells(i, 2).Value
    End If
    Next J
    Next i
    MsgBox "done"
    End Sub
    

    Best Regards,

    Lan

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Ed_Dao Thursday, May 28, 2015 5:17 PM
    Thursday, May 28, 2015 4:48 AM
    Moderator