Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code to open record's relative Excel workbook (Access 2000)

    May I have some advice, please. I have an Access 2000 database data entry form and report for producing client proposals (a report will be printed as the proposal). For each record being input into the table form there is a corresponding Excel workbook that was created from an Excel template with many static worksheets, formulas, functions, etc., for which much analytical work is done in producing figures for a proposal. The workbooks' spreadsheet named "Cost" has the final figures for three of the database table/form fields.

    Would it be possible to write a macro program in Access whereby the user is prompted to open the relevant Excel workbook and then the code automatically goes to the spreadsheet named "Cost" and picks up the values in 3 different static cells, F100, G100, and H100 and transfers the data from these three cells to the relevant database fields called, say, COST1, COST2, COST3, respectively?

    I've used "frmCommon" before in a Microsoft Word program to open an Excel workbook and was wondering if it can be used in Access code (I've not done any programming in Access!):

    On Error GoTo ErrorHandler
    Dim strDatabaseName As String
    'strDatabaseName = InputBox("Enter the full path name of your data source file, e.g., C:My Documentsdata.doc.")
    frmCommon.comdialogSave.DialogTitle = "Open Your Estimate Spreadsheet"
    frmCommon.comdialogSave.ShowOpen
    strDatabaseName = frmCommon.comdialogSave.FileName

    If strDatabaseName = "" Then
    End
    End If

    Any advice on how one would approach this task would be much appreciated. Many thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to open record's relative Excel workbook (Access 2000)

    Is there a way to determine automatically which workbook belongs to a record, or must the user always select the workbook manually?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to open record's relative Excel workbook (Access 2000)

    Hello, Hans,

    Regretfully, I don't think there is a way to determine the workbook automatically--there are 100+ clients, each client requests proposals for many different projects, each client has a folder on a network drive, and each project for the client has a folder where the Excel workbook is saved.

    I think the users will have to find the appropriate workbook and open it themselves. From there, though, because the workbooks are based on same template and the information can always be found on the same worksheet in all the workbooks and in the same cells on the worksheet. Let me know if I can explain more. Many thanks for your help, Hans!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to open record's relative Excel workbook (Access 2000)

    Atttached to <post#=304,810>post 304,810</post#>, you'll find an Access 97 database with a module and class module that you can import into your database. They provide an Open dialog that you can use to open a workbook. Code could look like this:

    Private Sub cmdExcel_Click()
    Dim strFile As String
    Dim dlg As New CommonDialog
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet

    On Error GoTo ErrHandler
    With dlg
    .Filter = "Excel workbooks (*.xls)|*.xls"
    If .OpenDialog = True Then
    strFile = .FileName
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open(FileName:=strFile, AddToMRU:=False)
    Set xlWsh = xlWbk.Worksheets("Cost")
    Me.Cost1 = xlWsh.Range("F100")
    Me.Cost2 = xlWsh.Range("G100")
    Me.Cost3 = xlWsh.Range("H100")
    Else
    MsgBox "Action canceled.", vbExclamation
    End If
    End With

    ExitHandler:
    On Error Resume Next
    Set dlg = Nothing
    Set xlWsh = Nothing
    xlWbk.Close SaveChanges:=False
    Set xlWbk = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    (This code is for a command button named cmdExcel, and it assumes that Cost1, Cost2 and Cost3 are fields in the record source of the form)

    Oh yes, I forgot to mention that you should select Tools | References... in the Visual Basic Editor and set a reference to the Microsoft Excel 9.0 Object Library. This is needed to control Excel from Access.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to open record's relative Excel workbook (Access 2000)

    Hans, thank you so much! Brilliant, as usual! Just looking at your code and trying the Open Dialog example in the post you referenced, I think it will do the trick. I won't be able to test it, though, until next week, but will let you know the results. (It's always amazing to me that you know what is needed even when some of us may not be too clear in communicating our problems--thank you, thank you, thank you!)

Posting Permissions

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