Results 1 to 2 of 2
  1. #1
    New Lounger Ensemble's Avatar
    Join Date
    Apr 2013
    Thanked 1 Time in 1 Post

    Question Setvalue of field on Access form to value of specific cell in excel form.


    I have a form as part of an access database. Amongst numerous other fields on the form (from Different tables), I have the following fields:
    [CNum] : Primary key (autonumber) from one table
    [Type] : Text field limmeted to 5 options.
    [CName] : Text field

    I receive data in the form of excel spread sheets (1 spreadsheet per entry in my main table) The spreadsheets are saved in a spesific location and named according to the relevant [Cnum] i.e. the spreadsheet for record 4949 is named C:/CC/4949.xls.

    Each spread sheet has 5 tabs corresponding to the 5 possible values of [Type]

    I need to create an event (Button double click) that will:

    Update [CName] with the value of cell B6 on the [Type] sheet of workbook [CNum]

    Any sugestions on the best way to accomplish this?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,608 Times in 1,452 Posts

    Here's some code I gen'd up that I hope will meet your needs. In my form I used Unbound fields since I didn't want to create a database but if you just substitute your form control names for the ones I used (I used your nominclature to make it easier) it should work fine.
    Option Compare Database
    Option Explicit
    #Const LateBinding = 0   'Early binding = 0 Late Binding = 1 Change for testing.
    'Note: Early Binding requires a Reference be set to Excel in Tools->References!
    Private Sub cmdUpdate_Click()
       Dim zType     As String
       Dim zFilePath As String
       zFilePath = "G:\BEKDocs\Excel\Test\"  'Replace w/your info
    #If LateBinding = 0 Then  'Early Binding
      Dim xlApp As Excel.Application
      Dim xlBook As Excel.Workbook
      Dim xlApp As Object
      Dim xlBook As Object
    #End If
    #If LateBinding = 0 Then
        Set xlApp = New Excel.Application
        Set xlApp = CreateObject("Excel.Application")
    #End If
        If xlApp Is Nothing Then
          MsgBox "Cannot open Excel."
          Exit Sub
        End If
        Set xlBook = xlApp.Workbooks.Open(zFilePath & tbCNum & ".xls")
        zType = tbType
        tbCName = xlApp.Sheets(zType).[B6].Value
       Set xlApp = Nothing
    End Sub   'cmdUpdate_Click()
    Note: the code is currently set to Early Binding if you want Late Binding change the compiler constant to 1 as noted in the comments.
    I tested both methods an both work in Access 2010 32-bit on Win 8 Pro 64-bit. HTH
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts