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

Topic

New Reply

Hi,

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?

Reply | Quote

Replies

Ensemble,

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.
34925-UpdateFromExcelForm

Code:

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:BEKDocsExcelTest"  'Replace w/your info
#If LateBinding = 0 Then  'Early Binding
  Dim xlApp As Excel.Application
  Dim xlBook As Excel.Workbook
#Else
  Dim xlApp As Object
  Dim xlBook As Object
#End If

#If LateBinding = 0 Then
    Set xlApp = New Excel.Application
#Else
    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
   xlApp.Quit
   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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote