Find and open Excel 97 file using Access

Topic

New Reply

Edited by ptebo on 01-Jun-01 17:05.

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).

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

here is the code for the function

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

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

Dim Dbl As Double
  
     Dbl = Shell("C:Program FilesMicrosoft OfficeOfficeEXCEL.EXE_ 
your serveryour folder", vbMaximizedFocus)

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

Reply | Quote

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.

Reply | Quote

The temporary directory for the Excel files is R:pteboFMP. 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.

Reply | Quote

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 = “R:pteboFMPQuote”
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 = “R:pteboFMPPart 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.

Reply | Quote

Your code worked perfectly. Thanks

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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?

Reply | Quote

yup – you’d need something like:
Sub XLFileOpen()
Dim xlApp As New Excel.Application
With xlApp
.Visible = True
.Dialogs(xlDialogOpen).Show “R:ptebo”
End With
Set xlApp = Nothing
End Sub
Hope that helps.

Reply | Quote

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.

Reply | Quote

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 = “R:ptebo” & 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 R:ptebo 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.

Reply | Quote

I have the same concerns regarding using customer names as folder names and will be discussing them with the department that has requested the database.

I do have one other question. (Hopefully this will be the last. Thanks for your patience.) Is there a subdirectory limit to the strFolder code? I’m trying to go one folder deeper in my strFolder path using the code that you gave me and when I run the code, the File Open screen that appears is my C: drive instead of the appropriate G: folder. The folder that I’m trying to open is g:publiccompanycustomerpart number. Using the code that you gave me, I can only get as far as the customer folder in the g: drive.

Reply | Quote

Hi,
It seems to work OK using 5 levels of folder – the fact that it’s taking you into C: would seem to imply that the path you’re looking for doesn’t exist. I’d check it carefully for typos. If that doesn’t help can you post your code exactly as it is?

Reply | Quote

I think that the main problem I’m having is that I don’t know what I’m doing as far as writing code goes. Here is the code that I’m trying. It returns the correct value when I rest my mouse on it in the code, but goes to my C: drive when I actually run the command.

Private Sub Command29_Click()
Dim xlApp As New Excel.Application
Dim strfolder As String
strfolder = “g:publicmoxnesscustomers” & Me!Customer Me!CustomerPartNo
With xlApp
.Visible = True
.Dialogs(xlDialogOpen).Show strfolder
End With
Set xlApp = Nothing
End Sub

Reply | Quote

Try changing the third line to:
strfolder = “g:publicmoxnesscustomers” & Me!Customer & “” & Me!CustomerPartNo
Hope that helps.

Reply | Quote

That worked. Thanks for all your help.

Reply | Quote

My pleasure – beats working!

Reply | Quote

As your path includes a space then you are entering your path in code such as “G:path with spaces” aren’t you? If not then it tries to find the path to the folder G:path and thinks with and spaces are some sort of argument.

Reply | Quote

I agree completely with Rory.

Check out this post 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:

    '...Open workbook
    XLApp.Sheets("YourSheetName").Select
    XLApp.ActiveSheet.Range("A1").CopyFromRecordset rst 
    'Substitute your recordset's name for "rst"
    '...

Post back if you need further help or explanation.

Reply | Quote