Results 1 to 2 of 2
2013-09-13, 18:06 #1
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?
2013-09-13, 19:23 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,457 Times in 1,326 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 #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()
I tested both methods an both work in Access 2010 32-bit on Win 8 Pro 64-bit. HTH