Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and open Excel 97 file using Access

    <P ID="edit"><FONT SIZE=-1>Edited by ptebo on 01-Jun-01 17:05.</FONT></P>Since I haven't received any responses, I'm going to guess that what I was after isn't possible. My next question then is can I download a specific record from Access to Excel? If I can do that, I'll do the find and open from Excel.

    I am creating a new quote tracking database using Access 97. The department that will be using this database wants to be able to select a field containing a specific quote number or part number and do a Find. They then want the Excel spreadsheet containing (and named after) that quote or part opened. The quotes and parts will be stored in a specific directory to help narrow down the Find. Is there a way using Access to find and open a specific Excel spreadsheet? (I have never written a macro or code in Access other than the AutoExec macro so any help you can give me will be appreciated).

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    not sure if this is what you want but
    i us a function to scan the folder and retrieve the names of the files in the folder
    then i put those file names in a list box so the user can click the list box to view the file
    if you think this will help i'll be glad to post the code

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    That would probably work. Is there any limitation to the number of files that can be listed?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    here is the code for the function

    <pre>Function Listdirs(fld As Control, id As Variant, _
    row As Variant, col As Variant, _
    code As Variant) As Variant
    Static dbs(10027) As String, Entries As Integer
    Dim ReturnVal As Variant
    ReturnVal = Null
    Select Case code
    Dim mypath, MyName
    Case acLBInitialize ' Initialize.
    Entries = 0
    mypath = "serverfolder" ' Set the path.
    MyName = DIR(mypath, vbDirectory) ' Retrieve the first entry.
    dbs(Entries) = ((mypath) + (MyName))
    Do Until dbs(Entries) = "" Or Entries >= 10027
    Entries = Entries + 1
    dbs(Entries) = DIR
    Loop
    ReturnVal = Entries
    Case acLBOpen ' Open.
    ' Generate unique ID for control.
    ReturnVal = Timer
    Case acLBGetRowCount ' Get number of rows.
    ReturnVal = Entries
    Case acLBGetColumnCount ' Get number of columns.
    ReturnVal = 1
    Case acLBGetColumnWidth ' Column width.
    ' -1 forces use of default width.
    ReturnVal = -1
    Case acLBGetValue ' Get data.
    ReturnVal = dbs(row)
    Case acLBEnd ' End.
    Erase dbs
    End Select
    Listdirs = ReturnVal
    End Function

    </pre>


    the code will return over 10,000 entries
    and it will retrieve any file in the folder
    you will need code in the list box to open the spread sheets
    this is the code i use to open a spread sheet
    <pre>Dim Dbl As Double

    Dbl = Shell("C:Program FilesMicrosoft OfficeOfficeEXCEL.EXE_
    your serveryour folder[list]", vbMaximizedFocus)
    </pre>

    i put this code on the on click of the list box
    it may not be very fancy but it works for me
    HTH

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Find and open Excel 97 file using Access

    Hi,
    If your Excel file is named exactly after the part or quote number (eg for quote number 1234 the file is called 1234.xls) then it would actually be quite straightforward to open that specific file, if they're all in the same directory. If you can provide a few more details (directory path and the name of the text box (or other control) that holds the quote/part number) I can probably knock up some code for you.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    The temporary directory for the Excel files is RteboFMP. There are separate subdirectories called Quote and Part Number. I'm going to have to move the files and change the referenced paths in the code after the file is moved to our appropriate divisions server.

    The Access text box containing the quote number is named QuoteNo; the part number is FMPItemNo. If the quote number is 2345, the Excel file will be 2345.xls. The same goes for the part number (FMPItemNo).

    Thanks for your help.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    I agree completely with Rory.

    Check out this <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=42519&page=1&vie w=expanded&sb=5>post</A> for the basic idea of opening an Excel spreadsheet from within Access. You'll have to create a string for the filename that combines the path of the files with the filename of the selected record.

    Regarding your second request, you can use Excel's CopyFromRecordset method to dump data from an Access (DAO or ADO) recordset to a specified range in Excel. After using Excel's Open command, you could do something like:<pre> '...Open workbook
    XLApp.Sheets("YourSheetName").Select
    XLApp.ActiveSheet.Range("A1").CopyFromRecordset rst
    'Substitute your recordset's name for "rst"
    '...</pre>

    Post back if you need further help or explanation.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Find and open Excel 97 file using Access

    Possibly the simplest thing is 2 separate buttons on your form - one for quote number and one for part number. In the OnClick event of the quote button you would use something like:
    Dim xlApp As Excel.Application, strPath As String
    strPath = "RteboFMPQuote"
    Set xlApp = New Excel.Application
    With xlApp
    .Visible = True
    .workbooks.Open strPath & Me!QuoteNo & ".xls"
    End With
    Set xlApp = Nothing
    and for the Part number:
    Dim xlApp As Excel.Application, strPath As String
    strPath = "RteboFMPPart Number"
    Set xlApp = New Excel.Application
    With xlApp
    .Visible = True
    .workbooks.Open strPath & Me!FMPItemNo & ".xls"
    End With
    Set xlApp = Nothing
    Hope that helps - any problems let me know. You will need to set a reference to Excel's Object library - when you're in the code window choose Tools-References and check Microsoft Excel 8 Object library.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    Your code worked perfectly. Thanks

  10. #10
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    One more question - will I have to activate the Microsoft Excel 8 Object library on all of the computers that will be using this database in order for them to use the command buttons that open the Excel spreadsheets?

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Find and open Excel 97 file using Access

    No - the reference is stored in the database itself - as long as all the machines have Excel 8 on them, you shouldn't have a problem.
    Glad it worked!
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    Any chance you can help me with something else? They've changed how they want to use the database (again!). They don't want specific Excel files to open, they want to stop at the Open screen so that the user can select which file they want opened. Is there a way to do this?

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Find and open Excel 97 file using Access

    yup - you'd need something like:
    Sub XLFileOpen()
    Dim xlApp As New Excel.Application
    With xlApp
    .Visible = True
    .Dialogs(xlDialogOpen).Show "Rtebo"
    End With
    Set xlApp = Nothing
    End Sub
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and open Excel 97 file using Access

    Great. One more little thing they've thrown at me - they've decided that they're going to keep most of the Excel information in individual customer folders instead of separate Quote and Item Number folders. How can I get to a File Open screen when the folder that I'm accessing varies depending on the customer that appears in my Customer text box?

    Thanks once again for your help.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Find and open Excel 97 file using Access

    OK, assuming that the folder will be named after the customer, you'd have something like:
    Sub XLFileOpen()
    Dim xlApp As New Excel.Application
    Dim strFolder as string
    strFolder = "Rtebo" & Me!txtCustomer
    With xlApp
    .Visible = True
    .Dialogs(xlDialogOpen).Show strFolder
    End With
    Set xlApp = Nothing
    End Sub
    You can alter the base path (i.e. the Rtebo bit) as appropriate. The folder name will need to be an exact match for the customer name though! You may need to establish a naming convention that uses something like Customer number rather than name for example as most users won't bother creating folders with the full company name! You could also add some error checking so that if the folder is not found it will pop up a message box to warn the user or simply default to a root directory.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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