Link an excel worksheet to a value in a text box on a form (1 Viewer)

[awade]

Good Evening,

I have an access 2010 database that looks after all the engine data for a fleet of engines. Each engine has a unique serial number and that serial number is displayed in a test box on a form using a search function.
Each engine serial number has a corresponding excel worksheet where the engine data is displayed as a graph.
Is there anyway that I can have a control button on the form that will allow me to display the excel spreadsheet corresponding to the engine serial number displayed in the text box?

Thanks in advance

 

[JHB]

How easy it is depend on what the name you use for the excel spreadsheet and what name/value you use in the form.
Something like below - code not tested. Remember to add the Excel references.

  Dim xlTmp As Excel.Application
  Set xlTmp = New Excel.Application
  xlTmp.Workbooks.Open ThePathAndTheExcelSpreadsheetName 
  xlTmp.Visible = True

 

[awade]

@JBH, Thanks for the reply. The Serial Number displayed in the text box is the same as the name of the excel worksheet.

So where do I add that code? Im very new to all of this, and learning as I go.

The command button will open the excel worksheet that corresponds to the serial number in the text box. So that way if the serial number in the text box changes, then it will open up the worksheet that corresponds to that serial number.

I have tried to have it hyperlinked but when the serial number changes I have to re establish the hyperlink.

What I am after is that it will automatically happen.

Thanks in advance.

 

[JHB]

The command button will open the excel worksheet that corresponds to the serial number in the text box...

What I am after is that it will automatically happen.

You do write about two different method - one where a button is activated, another where it should be automatic, what exactly do you want - or do I misunderstand something?

So where do I add that code?

The code is added under the button.

 

[awade]

@JHB The serial number in the text box can change from time to time. eg. Today the Serial Number in the text box is CAE540151. When I press the command button I want the excel workbook CAE540151 to open.

If I do an engine change tonight the serial number in the text box tomorrow will be for example, CAE540714. When I press the command button I want the excel workbook CAE540714 to open.

That is the automatic part I was trying to explain. I don't want to have to re establish hyperlinks, I just want what ever number is displayed in the text box to open the excel worksheet corresponding to that number.

Thanks and apologies for any confusion.

 

[awade]

Sorry meant to add the serial number that is displayed in the text box is updated through an excel spreadsheet that is linked to a table. The spreadsheet with all the engine serial numbers is updated by and external agency. The data from that spreadsheet id linked into the table and the table updates all the fields in the forms.

 

[JHB]

I've made an example for you in the attached database, remember to change the path, (in the code of the button), to where you store the Excel files.

 

[awade]

@JHB. Thanks for the sample. I have tried to run the sample database after changing the path.

I now get an error saying that A:\eDNA\eDNA Database CAE540151.xls can not be found, and access freezes!!!

Could this be that every serial number is in its own folder? and the folders are named the same as the engine serial number?

The text box I have with the serial numbers in it cant be a list box. as I would have to have all 58 engines in the list and that would be confusing for the user.

Can I have the table setup with all the serial numbers in it and as the serial numbers are changed in the master spreadsheet that is linked to this form then it will recognize if the numer has changed in that text box and only display the corresponding excel worksheet to the number in that text box?

 

[awade]

@JHB. I have tried A:\eDNA\eDNA Database\* is the path name, and it still does not open the folder associated with the worksheet of the serial number selected.
I made a dummy worksheet named CAE540714.xlsx and saved it in the same directory as the database and that works...
Is there anyway I can have a wildcard search of all the folders in that directory to look for the serial number worksheet associated to the serial number displayed in the text box?

Thanks for all your help so far.

 

[JHB]

It wonder me you are using drive "A", it is/was normally used for floppy drive and no one use floppies anymore! MS-Access will freeze if the drive doesn't exist.
In my sample I've used a combobox to be sure you put in the right name, you've to change that to your control.
If the spreadsheet is stored in a folder with the name of the serial number, you've to repeat your control in the code line.

  xlTmp.Workbooks.Open ThePath & "\" [B][COLOR=Red]& Me.SerialNumber & "\"[/COLOR][/B] & Me.SerialNumber & ".xls"

Remember also to change the file extension from .xls" to ".xlsx".

 

[awade]

@JBH. My "A:\" Drive is the network drive where the database and all the associated files are stored.

I have used the code you provided yesterday, and everything worked as it should I have now embedded it into the database and it works.

Thank you very much for all of your assistance on this. You have increased my knowledge of access just that little bit more.

 

[awade]

@JHB after running some more testing on the database I am now getting the error as follows;

"User-defined type not defined".

Below is the code I have against the command button.

The text box with the engine serial number in it is named "Core S/N" with the control source "Core S/N"

The Core S/N is updated by a linked excel spreadsheet.

Any ideas on why this isn't working now?

Private Sub OpenExcel_Click()
Dim xlTmp As Excel.Application, ThePath As String

ThePath = "A:\eDNA\eDNA Database"
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open ThePath & "\" & Me.Core S/N & "\" & Me.Core S/N & ".xlsx"
xlTmp.Visible = True
End Sub

 

[JHB]

In exactly which codeline do you get the error?
You need to surround control-/field-names with [] if they have spaces in their names. I'll suggest never using spaces or special characters in control-/field-names!

xlTmp.Workbooks.Open ThePath & "\" & Me.[B][COLOR=Red][[/COLOR][/B]Core S/N[B][COLOR=Red]][/COLOR][/B] & "\" & Me.[B][COLOR=Red][[/COLOR][/B]Core S/N[B][COLOR=Red]] [/COLOR][/B]& ".xlsx"

 

[awade]

@JHB
This is the area of the code where the error is
Private Sub OpenExcel_Click()
Dim xlTmp As Excel.Application, ThePath As String

Private Sub OpenExcel_Click() is highlighted yellow
xlTmp As Excel.Application is highlighted in blue.

Thanks for looking into this

 

[JHB]

Did you set the references to "Excel".
Open the code window, choose "Tools"->"References" and mark the "Microsoft Excel .. Object library"

 

[awade]

@JHB

I've checked the box, and now everything works.

Thanks once again for all of your help.

 

[JHB]

You're welcome, good luck!