Hi all,

I’m doing some database modification after being away from Access (and the
calibration database I created) for over six years. I’m looking at what I’ve
done and asking myself “How on earth did I do that???” Needless to say, I’m
forgetting an awful lot so please bear with me.

I’m trying to program a command button such that it will open an Excel
workbook to a specific spreadsheet depending on the equipment and serial
number displayed on the form. (This is only a temporary work around until I
get a chance to set this up in the database but we need something for the ISO
audit coming up.)

A little background…

The button is to be on main form whose record source is a query. The main
form displays the query results of equipment make and the serial number. When
scrolling through the records displayed on the main form, the sub-form will
display the associated calibration records for each piece of equipment
(record source is a table).

My goal …

When the user scrolls through and stops at the record for the “Fluke 45
DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
workbook will open to the spreadsheet for serial number 123456. If the user
then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
the workbook will open to the spreadsheet for serial number 98765. And so on.

I haven’t been able to get a macro to work and am thinking I must use code.
I did find code somewhere on this newsgroup to open a given spreadsheet but
now I need to modify it such that the particular sheet that opens is directly
related to the equipment make and serial number displayed on the main form.

Would I need to use a Select Case or perhaps incorporate the query that is
the record source for the main form? Am I biting off more than I can chew at
this time?

Thanks for any and all help!

Chris

Hi Chris,

are the Excel workbooks all in the same location or will the user need a
File Open dialog box to browse to a file?

"Would I need to use a Select Case"

No, it sounds like you can get the filename and sheetname from your data
-- just wondering about the path... and you should read this since you
will need to use VBA:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

we can help you with the code

Warm Regards,
Crystal


*
(: have an awesome day
*




CB wrote:
> Hi all,
>
> I’m doing some database modification after being away from Access (and the
> calibration database I created) for over six years. I’m looking at what I’ve
> done and asking myself “How on earth did I do that???” Needless to say, I’m
> forgetting an awful lot so please bear with me.
>
> I’m trying to program a command button such that it will open an Excel
> workbook to a specific spreadsheet depending on the equipment and serial
> number displayed on the form. (This is only a temporary work around until I
> get a chance to set this up in the database but we need something for the ISO
> audit coming up.)
>
> A little background…
>
> The button is to be on main form whose record source is a query. The main
> form displays the query results of equipment make and the serial number. When
> scrolling through the records displayed on the main form, the sub-form will
> display the associated calibration records for each piece of equipment
> (record source is a table).
>
> My goal …
>
> When the user scrolls through and stops at the record for the “Fluke 45
> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
> workbook will open to the spreadsheet for serial number 123456. If the user
> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
> the workbook will open to the spreadsheet for serial number 98765. And so on.
>
> I haven’t been able to get a macro to work and am thinking I must use code.
> I did find code somewhere on this newsgroup to open a given spreadsheet but
> now I need to modify it such that the particular sheet that opens is directly
> related to the equipment make and serial number displayed on the main form.
>
> Would I need to use a Select Case or perhaps incorporate the query that is
> the record source for the main form? Am I biting off more than I can chew at
> this time?
>
> Thanks for any and all help!
>
> Chris
>

Hi Crystal,

There is only one file. I need to reference different sheets within the one
file depending on what is displayed for "Equipment" and "Serial Number" in
the current record (result of a query).

Thanks for the link. I'll check it out.

Chris

"strive4peace" wrote:

> Hi Chris,
>
> are the Excel workbooks all in the same location or will the user need a
> File Open dialog box to browse to a file?
>
> "Would I need to use a Select Case"
>
> No, it sounds like you can get the filename and sheetname from your data
> -- just wondering about the path... and you should read this since you
> will need to use VBA:
>
> Access Basics
> 8-part free tutorial that covers essentials in Access
> http://www.AccessMVP.com/strive4peace
>
> we can help you with the code
>
> Warm Regards,
> Crystal
>
>
> *
> (: have an awesome day
> *
>
>
>
>
> CB wrote:
> > Hi all,
> >
> > I’m doing some database modification after being away from Access (and the
> > calibration database I created) for over six years. I’m looking at what I’ve
> > done and asking myself “How on earth did I do that???” Needless to say, I’m
> > forgetting an awful lot so please bear with me.
> >
> > I’m trying to program a command button such that it will open an Excel
> > workbook to a specific spreadsheet depending on the equipment and serial
> > number displayed on the form. (This is only a temporary work around until I
> > get a chance to set this up in the database but we need something for the ISO
> > audit coming up.)
> >
> > A little background…
> >
> > The button is to be on main form whose record source is a query. The main
> > form displays the query results of equipment make and the serial number. When
> > scrolling through the records displayed on the main form, the sub-form will
> > display the associated calibration records for each piece of equipment
> > (record source is a table).
> >
> > My goal …
> >
> > When the user scrolls through and stops at the record for the “Fluke 45
> > DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
> > workbook will open to the spreadsheet for serial number 123456. If the user
> > then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
> > the workbook will open to the spreadsheet for serial number 98765. And so on.
> >
> > I haven’t been able to get a macro to work and am thinking I must use code.
> > I did find code somewhere on this newsgroup to open a given spreadsheet but
> > now I need to modify it such that the particular sheet that opens is directly
> > related to the equipment make and serial number displayed on the main form.
> >
> > Would I need to use a Select Case or perhaps incorporate the query that is
> > the record source for the main form? Am I biting off more than I can chew at
> > this time?
> >
> > Thanks for any and all help!
> >
> > Chris
> >
>

Hi Chris,

you're welcome

what is the file name and path?

what are a few of your sheet names?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




CB wrote:
> Hi Crystal,
>
> There is only one file. I need to reference different sheets within the one
> file depending on what is displayed for "Equipment" and "Serial Number" in
> the current record (result of a query).
>
> Thanks for the link. I'll check it out.
>
> Chris
>
> "strive4peace" wrote:
>
>> Hi Chris,
>>
>> are the Excel workbooks all in the same location or will the user need a
>> File Open dialog box to browse to a file?
>>
>> "Would I need to use a Select Case"
>>
>> No, it sounds like you can get the filename and sheetname from your data
>> -- just wondering about the path... and you should read this since you
>> will need to use VBA:
>>
>> Access Basics
>> 8-part free tutorial that covers essentials in Access
>> http://www.AccessMVP.com/strive4peace
>>
>> we can help you with the code
>>
>> Warm Regards,
>> Crystal
>>
>>
>> *
>> (: have an awesome day
>> *
>>
>>
>>
>>
>> CB wrote:
>>> Hi all,
>>>
>>> I’m doing some database modification after being away from Access (and the
>>> calibration database I created) for over six years. I’m looking at what I’ve
>>> done and asking myself “How on earth did I do that???” Needless to say, I’m
>>> forgetting an awful lot so please bear with me.
>>>
>>> I’m trying to program a command button such that it will open an Excel
>>> workbook to a specific spreadsheet depending on the equipment and serial
>>> number displayed on the form. (This is only a temporary work around until I
>>> get a chance to set this up in the database but we need something for the ISO
>>> audit coming up.)
>>>
>>> A little background…
>>>
>>> The button is to be on main form whose record source is a query. The main
>>> form displays the query results of equipment make and the serial number. When
>>> scrolling through the records displayed on the main form, the sub-form will
>>> display the associated calibration records for each piece of equipment
>>> (record source is a table).
>>>
>>> My goal …
>>>
>>> When the user scrolls through and stops at the record for the “Fluke 45
>>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
>>> workbook will open to the spreadsheet for serial number 123456. If the user
>>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
>>> the workbook will open to the spreadsheet for serial number 98765. And so on.
>>>
>>> I haven’t been able to get a macro to work and am thinking I must use code.
>>> I did find code somewhere on this newsgroup to open a given spreadsheet but
>>> now I need to modify it such that the particular sheet that opens is directly
>>> related to the equipment make and serial number displayed on the main form.
>>>
>>> Would I need to use a Select Case or perhaps incorporate the query that is
>>> the record source for the main form? Am I biting off more than I can chew at
>>> this time?
>>>
>>> Thanks for any and all help!
>>>
>>> Chris
>>>

Hi Crystal,

The filename is DMM_Verifications.xls and the path is
\\ISO\Inspection\Depot\Digital MultiMeter Verification Records.

Some sheet names include Jones_179, Smith_87_III, Hamil_87_III

Regards,
Chris

"strive4peace" wrote:

> Hi Chris,
>
> you're welcome
>
> what is the file name and path?
>
> what are a few of your sheet names?
>
> Warm Regards,
> Crystal
>
> remote programming and training
>
> Access Basics
> 8-part free tutorial that covers essentials in Access
> http://www.AccessMVP.com/strive4peace
>
> *
> (: have an awesome day
> *
>
>
>
>
> CB wrote:
> > Hi Crystal,
> >
> > There is only one file. I need to reference different sheets within the one
> > file depending on what is displayed for "Equipment" and "Serial Number" in
> > the current record (result of a query).
> >
> > Thanks for the link. I'll check it out.
> >
> > Chris
> >
> > "strive4peace" wrote:
> >
> >> Hi Chris,
> >>
> >> are the Excel workbooks all in the same location or will the user need a
> >> File Open dialog box to browse to a file?
> >>
> >> "Would I need to use a Select Case"
> >>
> >> No, it sounds like you can get the filename and sheetname from your data
> >> -- just wondering about the path... and you should read this since you
> >> will need to use VBA:
> >>
> >> Access Basics
> >> 8-part free tutorial that covers essentials in Access
> >> http://www.AccessMVP.com/strive4peace
> >>
> >> we can help you with the code
> >>
> >> Warm Regards,
> >> Crystal
> >>
> >>
> >> *
> >> (: have an awesome day
> >> *
> >>
> >>
> >>
> >>
> >> CB wrote:
> >>> Hi all,
> >>>
> >>> I’m doing some database modification after being away from Access (and the
> >>> calibration database I created) for over six years. I’m looking at what I’ve
> >>> done and asking myself “How on earth did I do that???” Needless to say, I’m
> >>> forgetting an awful lot so please bear with me.
> >>>
> >>> I’m trying to program a command button such that it will open an Excel
> >>> workbook to a specific spreadsheet depending on the equipment and serial
> >>> number displayed on the form. (This is only a temporary work around until I
> >>> get a chance to set this up in the database but we need something for the ISO
> >>> audit coming up.)
> >>>
> >>> A little background…
> >>>
> >>> The button is to be on main form whose record source is a query. The main
> >>> form displays the query results of equipment make and the serial number. When
> >>> scrolling through the records displayed on the main form, the sub-form will
> >>> display the associated calibration records for each piece of equipment
> >>> (record source is a table).
> >>>
> >>> My goal …
> >>>
> >>> When the user scrolls through and stops at the record for the “Fluke 45
> >>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
> >>> workbook will open to the spreadsheet for serial number 123456. If the user
> >>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
> >>> the workbook will open to the spreadsheet for serial number 98765. And so on.
> >>>
> >>> I haven’t been able to get a macro to work and am thinking I must use code.
> >>> I did find code somewhere on this newsgroup to open a given spreadsheet but
> >>> now I need to modify it such that the particular sheet that opens is directly
> >>> related to the equipment make and serial number displayed on the main form.
> >>>
> >>> Would I need to use a Select Case or perhaps incorporate the query that is
> >>> the record source for the main form? Am I biting off more than I can chew at
> >>> this time?
> >>>
> >>> Thanks for any and all help!
> >>>
> >>> Chris
> >>>
>

Hi Chris,

how do the sheet names correlate to your data?
ie:
1. EquipmentBrand_fieldname
2. _
3. SerialNumber_fieldname

if this is right, what are the respective fieldnames? If not, what is
the correct rule?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




CB wrote:
> Hi Crystal,
>
> The filename is DMM_Verifications.xls and the path is
> \\ISO\Inspection\Depot\Digital MultiMeter Verification Records.
>
> Some sheet names include Jones_179, Smith_87_III, Hamil_87_III
>
> Regards,
> Chris
>
> "strive4peace" wrote:
>
>> Hi Chris,
>>
>> you're welcome
>>
>> what is the file name and path?
>>
>> what are a few of your sheet names?
>>
>> Warm Regards,
>> Crystal
>>
>> remote programming and training
>>
>> Access Basics
>> 8-part free tutorial that covers essentials in Access
>> http://www.AccessMVP.com/strive4peace
>>
>> *
>> (: have an awesome day
>> *
>>
>>
>>
>>
>> CB wrote:
>>> Hi Crystal,
>>>
>>> There is only one file. I need to reference different sheets within the one
>>> file depending on what is displayed for "Equipment" and "Serial Number" in
>>> the current record (result of a query).
>>>
>>> Thanks for the link. I'll check it out.
>>>
>>> Chris
>>>
>>> "strive4peace" wrote:
>>>
>>>> Hi Chris,
>>>>
>>>> are the Excel workbooks all in the same location or will the user need a
>>>> File Open dialog box to browse to a file?
>>>>
>>>> "Would I need to use a Select Case"
>>>>
>>>> No, it sounds like you can get the filename and sheetname from your data
>>>> -- just wondering about the path... and you should read this since you
>>>> will need to use VBA:
>>>>
>>>> Access Basics
>>>> 8-part free tutorial that covers essentials in Access
>>>> http://www.AccessMVP.com/strive4peace
>>>>
>>>> we can help you with the code
>>>>
>>>> Warm Regards,
>>>> Crystal
>>>>
>>>>
>>>> *
>>>> (: have an awesome day
>>>> *
>>>>
>>>>
>>>>
>>>>
>>>> CB wrote:
>>>>> Hi all,
>>>>>
>>>>> I’m doing some database modification after being away from Access (and the
>>>>> calibration database I created) for over six years. I’m looking at what I’ve
>>>>> done and asking myself “How on earth did I do that???” Needless to say, I’m
>>>>> forgetting an awful lot so please bear with me.
>>>>>
>>>>> I’m trying to program a command button such that it will open an Excel
>>>>> workbook to a specific spreadsheet depending on the equipment and serial
>>>>> number displayed on the form. (This is only a temporary work around until I
>>>>> get a chance to set this up in the database but we need something for the ISO
>>>>> audit coming up.)
>>>>>
>>>>> A little background…
>>>>>
>>>>> The button is to be on main form whose record source is a query. The main
>>>>> form displays the query results of equipment make and the serial number. When
>>>>> scrolling through the records displayed on the main form, the sub-form will
>>>>> display the associated calibration records for each piece of equipment
>>>>> (record source is a table).
>>>>>
>>>>> My goal …
>>>>>
>>>>> When the user scrolls through and stops at the record for the “Fluke 45
>>>>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
>>>>> workbook will open to the spreadsheet for serial number 123456. If the user
>>>>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
>>>>> the workbook will open to the spreadsheet for serial number 98765. And so on.
>>>>>
>>>>> I haven’t been able to get a macro to work and am thinking I must use code.
>>>>> I did find code somewhere on this newsgroup to open a given spreadsheet but
>>>>> now I need to modify it such that the particular sheet that opens is directly
>>>>> related to the equipment make and serial number displayed on the main form.
>>>>>
>>>>> Would I need to use a Select Case or perhaps incorporate the query that is
>>>>> the record source for the main form? Am I biting off more than I can chew at
>>>>> this time?
>>>>>
>>>>> Thanks for any and all help!
>>>>>
>>>>> Chris
>>>>>

Hi again,

My database field names are per your "i.e." below; however, the Excel
worksheets are labelled differently. This was done by someone else to make it
easier to ensure the techs to open the appropriate sheet when they enter
their data. The general format for the sheet labelling is "TechSurname
EquipmentModel".

When I was first trying to figure this out, I was wondering if would be
helpful to add a field "WorksheetName" to the equipment table. Would this
help??

Chris

"strive4peace" wrote:

> Hi Chris,
>
> how do the sheet names correlate to your data?
> ie:
> 1. EquipmentBrand_fieldname
> 2. _
> 3. SerialNumber_fieldname
>
> if this is right, what are the respective fieldnames? If not, what is
> the correct rule?
>
> Warm Regards,
> Crystal
>
> remote programming and training
>
> Access Basics
> 8-part free tutorial that covers essentials in Access
> http://www.AccessMVP.com/strive4peace
>
> *
> (: have an awesome day
> *
>
>
>
>
> CB wrote:
> > Hi Crystal,
> >
> > The filename is DMM_Verifications.xls and the path is
> > \\ISO\Inspection\Depot\Digital MultiMeter Verification Records.
> >
> > Some sheet names include Jones_179, Smith_87_III, Hamil_87_III
> >
> > Regards,
> > Chris
> >
> > "strive4peace" wrote:
> >
> >> Hi Chris,
> >>
> >> you're welcome
> >>
> >> what is the file name and path?
> >>
> >> what are a few of your sheet names?
> >>
> >> Warm Regards,
> >> Crystal
> >>
> >> remote programming and training
> >>
> >> Access Basics
> >> 8-part free tutorial that covers essentials in Access
> >> http://www.AccessMVP.com/strive4peace
> >>
> >> *
> >> (: have an awesome day
> >> *
> >>
> >>
> >>
> >>
> >> CB wrote:
> >>> Hi Crystal,
> >>>
> >>> There is only one file. I need to reference different sheets within the one
> >>> file depending on what is displayed for "Equipment" and "Serial Number" in
> >>> the current record (result of a query).
> >>>
> >>> Thanks for the link. I'll check it out.
> >>>
> >>> Chris
> >>>
> >>> "strive4peace" wrote:
> >>>
> >>>> Hi Chris,
> >>>>
> >>>> are the Excel workbooks all in the same location or will the user need a
> >>>> File Open dialog box to browse to a file?
> >>>>
> >>>> "Would I need to use a Select Case"
> >>>>
> >>>> No, it sounds like you can get the filename and sheetname from your data
> >>>> -- just wondering about the path... and you should read this since you
> >>>> will need to use VBA:
> >>>>
> >>>> Access Basics
> >>>> 8-part free tutorial that covers essentials in Access
> >>>> http://www.AccessMVP.com/strive4peace
> >>>>
> >>>> we can help you with the code
> >>>>
> >>>> Warm Regards,
> >>>> Crystal
> >>>>
> >>>>
> >>>> *
> >>>> (: have an awesome day
> >>>> *
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> CB wrote:
> >>>>> Hi all,
> >>>>>
> >>>>> I’m doing some database modification after being away from Access (and the
> >>>>> calibration database I created) for over six years. I’m looking at what I’ve
> >>>>> done and asking myself “How on earth did I do that???” Needless to say, I’m
> >>>>> forgetting an awful lot so please bear with me.
> >>>>>
> >>>>> I’m trying to program a command button such that it will open an Excel
> >>>>> workbook to a specific spreadsheet depending on the equipment and serial
> >>>>> number displayed on the form. (This is only a temporary work around until I
> >>>>> get a chance to set this up in the database but we need something for the ISO
> >>>>> audit coming up.)
> >>>>>
> >>>>> A little background…
> >>>>>
> >>>>> The button is to be on main form whose record source is a query. The main
> >>>>> form displays the query results of equipment make and the serial number. When
> >>>>> scrolling through the records displayed on the main form, the sub-form will
> >>>>> display the associated calibration records for each piece of equipment
> >>>>> (record source is a table).
> >>>>>
> >>>>> My goal …
> >>>>>
> >>>>> When the user scrolls through and stops at the record for the “Fluke 45
> >>>>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
> >>>>> workbook will open to the spreadsheet for serial number 123456. If the user
> >>>>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
> >>>>> the workbook will open to the spreadsheet for serial number 98765. And so on.
> >>>>>
> >>>>> I haven’t been able to get a macro to work and am thinking I must use code.
> >>>>> I did find code somewhere on this newsgroup to open a given spreadsheet but
> >>>>> now I need to modify it such that the particular sheet that opens is directly
> >>>>> related to the equipment make and serial number displayed on the main form.
> >>>>>
> >>>>> Would I need to use a Select Case or perhaps incorporate the query that is
> >>>>> the record source for the main form? Am I biting off more than I can chew at
> >>>>> this time?
> >>>>>
> >>>>> Thanks for any and all help!
> >>>>>
> >>>>> Chris
> >>>>>
>

Hi Chris,

there is no reason to add a worksheet name if you can construct it from
your data...

here is the statement you will need:

Application.FollowHyperlink "C:\path\filename.xls", "SheetName!Address"

so, in code we can do this:

'~~~~~~~~~~~~~~~~
dim mFilename as string _
, mSheetname as string

mFilename = "\\ISO\Inspection\Depot\Digital MultiMeter Verification
Records\DMM_Verifications.xls"

mSheetname = "we need to work on this"

Application.FollowHyperlink _
mFilename , mSheetName & "!A1"
'~~~~~~~~~~~~~~~~

so, now we just have to write the code to calculate the sheetname by
referencing the controls that contain the following fields on your
form/subforms...

TechSurname
EquipmentModel

where are they located in relationship to where the code is going?

1. subform controlname
2. controlname


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




CB wrote:
> Hi again,
>
> My database field names are per your "i.e." below; however, the Excel
> worksheets are labelled differently. This was done by someone else to make it
> easier to ensure the techs to open the appropriate sheet when they enter
> their data. The general format for the sheet labelling is "TechSurname
> EquipmentModel".
>
> When I was first trying to figure this out, I was wondering if would be
> helpful to add a field "WorksheetName" to the equipment table. Would this
> help??
>
> Chris
>
> "strive4peace" wrote:
>
>> Hi Chris,
>>
>> how do the sheet names correlate to your data?
>> ie:
>> 1. EquipmentBrand_fieldname
>> 2. _
>> 3. SerialNumber_fieldname
>>
>> if this is right, what are the respective fieldnames? If not, what is
>> the correct rule?
>>
>> Warm Regards,
>> Crystal
>>
>> remote programming and training
>>
>> Access Basics
>> 8-part free tutorial that covers essentials in Access
>> http://www.AccessMVP.com/strive4peace
>>
>> *
>> (: have an awesome day
>> *
>>
>>
>>
>>
>> CB wrote:
>>> Hi Crystal,
>>>
>>> The filename is DMM_Verifications.xls and the path is
>>> \\ISO\Inspection\Depot\Digital MultiMeter Verification Records.
>>>
>>> Some sheet names include Jones_179, Smith_87_III, Hamil_87_III
>>>
>>> Regards,
>>> Chris
>>>
>>> "strive4peace" wrote:
>>>
>>>> Hi Chris,
>>>>
>>>> you're welcome
>>>>
>>>> what is the file name and path?
>>>>
>>>> what are a few of your sheet names?
>>>>
>>>> Warm Regards,
>>>> Crystal
>>>>
>>>> remote programming and training
>>>>
>>>> Access Basics
>>>> 8-part free tutorial that covers essentials in Access
>>>> http://www.AccessMVP.com/strive4peace
>>>>
>>>> *
>>>> (: have an awesome day
>>>> *
>>>>
>>>>
>>>>
>>>>
>>>> CB wrote:
>>>>> Hi Crystal,
>>>>>
>>>>> There is only one file. I need to reference different sheets within the one
>>>>> file depending on what is displayed for "Equipment" and "Serial Number" in
>>>>> the current record (result of a query).
>>>>>
>>>>> Thanks for the link. I'll check it out.
>>>>>
>>>>> Chris
>>>>>
>>>>> "strive4peace" wrote:
>>>>>
>>>>>> Hi Chris,
>>>>>>
>>>>>> are the Excel workbooks all in the same location or will the user need a
>>>>>> File Open dialog box to browse to a file?
>>>>>>
>>>>>> "Would I need to use a Select Case"
>>>>>>
>>>>>> No, it sounds like you can get the filename and sheetname from your data
>>>>>> -- just wondering about the path... and you should read this since you
>>>>>> will need to use VBA:
>>>>>>
>>>>>> Access Basics
>>>>>> 8-part free tutorial that covers essentials in Access
>>>>>> http://www.AccessMVP.com/strive4peace
>>>>>>
>>>>>> we can help you with the code
>>>>>>
>>>>>> Warm Regards,
>>>>>> Crystal
>>>>>>
>>>>>>
>>>>>> *
>>>>>> (: have an awesome day
>>>>>> *
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> CB wrote:
>>>>>>> Hi all,
>>>>>>>
>>>>>>> I’m doing some database modification after being away from Access (and the
>>>>>>> calibration database I created) for over six years. I’m looking at what I’ve
>>>>>>> done and asking myself “How on earth did I do that???” Needless to say, I’m
>>>>>>> forgetting an awful lot so please bear with me.
>>>>>>>
>>>>>>> I’m trying to program a command button such that it will open an Excel
>>>>>>> workbook to a specific spreadsheet depending on the equipment and serial
>>>>>>> number displayed on the form. (This is only a temporary work around until I
>>>>>>> get a chance to set this up in the database but we need something for the ISO
>>>>>>> audit coming up.)
>>>>>>>
>>>>>>> A little background…
>>>>>>>
>>>>>>> The button is to be on main form whose record source is a query. The main
>>>>>>> form displays the query results of equipment make and the serial number. When
>>>>>>> scrolling through the records displayed on the main form, the sub-form will
>>>>>>> display the associated calibration records for each piece of equipment
>>>>>>> (record source is a table).
>>>>>>>
>>>>>>> My goal …
>>>>>>>
>>>>>>> When the user scrolls through and stops at the record for the “Fluke 45
>>>>>>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
>>>>>>> workbook will open to the spreadsheet for serial number 123456. If the user
>>>>>>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
>>>>>>> the workbook will open to the spreadsheet for serial number 98765. And so on.
>>>>>>>
>>>>>>> I haven’t been able to get a macro to work and am thinking I must use code.
>>>>>>> I did find code somewhere on this newsgroup to open a given spreadsheet but
>>>>>>> now I need to modify it such that the particular sheet that opens is directly
>>>>>>> related to the equipment make and serial number displayed on the main form.
>>>>>>>
>>>>>>> Would I need to use a Select Case or perhaps incorporate the query that is
>>>>>>> the record source for the main form? Am I biting off more than I can chew at
>>>>>>> this time?
>>>>>>>
>>>>>>> Thanks for any and all help!
>>>>>>>
>>>>>>> Chris
>>>>>>>

Hi Crystal,

The name for my subform is simply “subfrm”. Creative, huh?! 

The name for the control for the equipment is “txtEquipment”. This control
is a textbox found on both the main form (named “Enter Primary Equipment
Calibration History”) and the subform.

The name for the control for the techs’ surnames is “txtTSOfficer”. FWIW,
this control is on the sub-form and is a combo box that queries the
“Inspectors” table and returns just their initials (the table also contains
their full name).

Just so you’re clear, I’ll give an example…

Let’s say John Smith has a Fluke 87-III multimeter. In the “Inspectors”
table, he is recorded by both “John Smith” and “JS”. He shows up in the
underlying records by his initials. The equipment is recorded in the records
as “Fluke 87-III Multimeter.” The spreadsheet that I need to launch would be
named “Smith 87 III”.

If these different iterations are problematic, it would be easy enough to
quickly modify either field names/data in the database or the names of the
sheets as I only have 15 records thus far.

Thanks so much for all your help! I really appreciate it.

Chris


"strive4peace" wrote:

> Hi Chris,
>
> there is no reason to add a worksheet name if you can construct it from
> your data...
>
> here is the statement you will need:
>
> Application.FollowHyperlink "C:\path\filename.xls", "SheetName!Address"
>
> so, in code we can do this:
>
> '~~~~~~~~~~~~~~~~
> dim mFilename as string _
> , mSheetname as string
>
> mFilename = "\\ISO\Inspection\Depot\Digital MultiMeter Verification
> Records\DMM_Verifications.xls"
>
> mSheetname = "we need to work on this"
>
> Application.FollowHyperlink _
> mFilename , mSheetName & "!A1"
> '~~~~~~~~~~~~~~~~
>
> so, now we just have to write the code to calculate the sheetname by
> referencing the controls that contain the following fields on your
> form/subforms...
>
> TechSurname
> EquipmentModel
>
> where are they located in relationship to where the code is going?
>
> 1. subform controlname
> 2. controlname
>
>
> Warm Regards,
> Crystal
>
> remote programming and training
>
> Access Basics
> 8-part free tutorial that covers essentials in Access
> http://www.AccessMVP.com/strive4peace
>
> *
> (: have an awesome day
> *
>
>
>
>
> CB wrote:
> > Hi again,
> >
> > My database field names are per your "i.e." below; however, the Excel
> > worksheets are labelled differently. This was done by someone else to make it
> > easier to ensure the techs to open the appropriate sheet when they enter
> > their data. The general format for the sheet labelling is "TechSurname
> > EquipmentModel".
> >
> > When I was first trying to figure this out, I was wondering if would be
> > helpful to add a field "WorksheetName" to the equipment table. Would this
> > help??
> >
> > Chris
> >
> > "strive4peace" wrote:
> >
> >> Hi Chris,
> >>
> >> how do the sheet names correlate to your data?
> >> ie:
> >> 1. EquipmentBrand_fieldname
> >> 2. _
> >> 3. SerialNumber_fieldname
> >>
> >> if this is right, what are the respective fieldnames? If not, what is
> >> the correct rule?
> >>
> >> Warm Regards,
> >> Crystal
> >>
> >> remote programming and training
> >>
> >> Access Basics
> >> 8-part free tutorial that covers essentials in Access
> >> http://www.AccessMVP.com/strive4peace
> >>
> >> *
> >> (: have an awesome day
> >> *
> >>
> >>
> >>
> >>
> >> CB wrote:
> >>> Hi Crystal,
> >>>
> >>> The filename is DMM_Verifications.xls and the path is
> >>> \\ISO\Inspection\Depot\Digital MultiMeter Verification Records.
> >>>
> >>> Some sheet names include Jones_179, Smith_87_III, Hamil_87_III
> >>>
> >>> Regards,
> >>> Chris
> >>>
> >>> "strive4peace" wrote:
> >>>
> >>>> Hi Chris,
> >>>>
> >>>> you're welcome
> >>>>
> >>>> what is the file name and path?
> >>>>
> >>>> what are a few of your sheet names?
> >>>>
> >>>> Warm Regards,
> >>>> Crystal
> >>>>
> >>>> remote programming and training
> >>>>
> >>>> Access Basics
> >>>> 8-part free tutorial that covers essentials in Access
> >>>> http://www.AccessMVP.com/strive4peace
> >>>>
> >>>> *
> >>>> (: have an awesome day
> >>>> *
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> CB wrote:
> >>>>> Hi Crystal,
> >>>>>
> >>>>> There is only one file. I need to reference different sheets within the one
> >>>>> file depending on what is displayed for "Equipment" and "Serial Number" in
> >>>>> the current record (result of a query).
> >>>>>
> >>>>> Thanks for the link. I'll check it out.
> >>>>>
> >>>>> Chris
> >>>>>
> >>>>> "strive4peace" wrote:
> >>>>>
> >>>>>> Hi Chris,
> >>>>>>
> >>>>>> are the Excel workbooks all in the same location or will the user need a
> >>>>>> File Open dialog box to browse to a file?
> >>>>>>
> >>>>>> "Would I need to use a Select Case"
> >>>>>>
> >>>>>> No, it sounds like you can get the filename and sheetname from your data
> >>>>>> -- just wondering about the path... and you should read this since you
> >>>>>> will need to use VBA:
> >>>>>>
> >>>>>> Access Basics
> >>>>>> 8-part free tutorial that covers essentials in Access
> >>>>>> http://www.AccessMVP.com/strive4peace
> >>>>>>
> >>>>>> we can help you with the code
> >>>>>>
> >>>>>> Warm Regards,
> >>>>>> Crystal
> >>>>>>
> >>>>>>
> >>>>>> *
> >>>>>> (: have an awesome day
> >>>>>> *
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> CB wrote:
> >>>>>>> Hi all,
> >>>>>>>
> >>>>>>> I’m doing some database modification after being away from Access (and the
> >>>>>>> calibration database I created) for over six years. I’m looking at what I’ve
> >>>>>>> done and asking myself “How on earth did I do that???” Needless to say, I’m
> >>>>>>> forgetting an awful lot so please bear with me.
> >>>>>>>
> >>>>>>> I’m trying to program a command button such that it will open an Excel
> >>>>>>> workbook to a specific spreadsheet depending on the equipment and serial
> >>>>>>> number displayed on the form. (This is only a temporary work around until I
> >>>>>>> get a chance to set this up in the database but we need something for the ISO
> >>>>>>> audit coming up.)
> >>>>>>>
> >>>>>>> A little background…
> >>>>>>>
> >>>>>>> The button is to be on main form whose record source is a query. The main
> >>>>>>> form displays the query results of equipment make and the serial number. When
> >>>>>>> scrolling through the records displayed on the main form, the sub-form will
> >>>>>>> display the associated calibration records for each piece of equipment
> >>>>>>> (record source is a table).
> >>>>>>>
> >>>>>>> My goal …
> >>>>>>>
> >>>>>>> When the user scrolls through and stops at the record for the “Fluke 45
> >>>>>>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
> >>>>>>> workbook will open to the spreadsheet for serial number 123456. If the user
> >>>>>>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
> >>>>>>> the workbook will open to the spreadsheet for serial number 98765. And so on.
> >>>>>>>
> >>>>>>> I haven’t been able to get a macro to work and am thinking I must use code.
> >>>>>>> I did find code somewhere on this newsgroup to open a given spreadsheet but
> >>>>>>> now I need to modify it such that the particular sheet that opens is directly
> >>>>>>> related to the equipment make and serial number displayed on the main form.
> >>>>>>>
> >>>>>>> Would I need to use a Select Case or perhaps incorporate the query that is
> >>>>>>> the record source for the main form? Am I biting off more than I can chew at
> >>>>>>> this time?
> >>>>>>>
> >>>>>>> Thanks for any and all help!
> >>>>>>>
> >>>>>>> Chris
> >>>>>>>
>

Hi Chris,

you're welcome

try this:

'~~~~~~~~~~~~~~~~
dim mFilename as string _
, mSheetname as string

'make sure data that you need is filled out
if IsNull(me.subfrm.form.txtTSOfficer) then
msgbox "You have not specified a tech",,"Cannot open Excel"
exit sub
end if

if IsNull(me.txtEquipment) then
msgbox "You have not specified equipment",,"Cannot open Excel"
exit sub
end if

mFilename = "\\ISO\Inspection\Depot\" _
& "Digital MultiMeter Verification Records\" _
& "DMM_Verifications.xls"

mSheetname = me.subfrm.form.txtTSOfficer _
& "_" & me.txtEquipment

if MsgBox( _
"Sheetname is: " & mSheetname _
& vbCrLf & vbCrLf & "Click Yes to Open" _
, vbYesNo, "Open sheet?") _
= vbYes then

Application.FollowHyperlink _
mFilename , mSheetName & "!A1"

end if

'~~~~~~~~~~~~~~~~

not sure if I got the sheetname properly constructed... probably the
txtTSOfficer needs to get a column from the combo ... but this is a
start anyway

It would be better to use the initials in the sheetname in case you have
equipment that has a long number so the length of the sheetname doesn't
go over the limit

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




CB wrote:
> Hi Crystal,
>
> The name for my subform is simply “subfrm”. Creative, huh?! 
>
> The name for the control for the equipment is “txtEquipment”. This control
> is a textbox found on both the main form (named “Enter Primary Equipment
> Calibration History”) and the subform.
>
> The name for the control for the techs’ surnames is “txtTSOfficer”. FWIW,
> this control is on the sub-form and is a combo box that queries the
> “Inspectors” table and returns just their initials (the table also contains
> their full name).
>
> Just so you’re clear, I’ll give an example…
>
> Let’s say John Smith has a Fluke 87-III multimeter. In the “Inspectors”
> table, he is recorded by both “John Smith” and “JS”. He shows up in the
> underlying records by his initials. The equipment is recorded in the records
> as “Fluke 87-III Multimeter.” The spreadsheet that I need to launch would be
> named “Smith 87 III”.
>
> If these different iterations are problematic, it would be easy enough to
> quickly modify either field names/data in the database or the names of the
> sheets as I only have 15 records thus far.
>
> Thanks so much for all your help! I really appreciate it.
>
> Chris
>
>
> "strive4peace" wrote:
>
>> Hi Chris,
>>
>> there is no reason to add a worksheet name if you can construct it from
>> your data...
>>
>> here is the statement you will need:
>>
>> Application.FollowHyperlink "C:\path\filename.xls", "SheetName!Address"
>>
>> so, in code we can do this:
>>
>> '~~~~~~~~~~~~~~~~
>> dim mFilename as string _
>> , mSheetname as string
>>
>> mFilename = "\\ISO\Inspection\Depot\Digital MultiMeter Verification
>> Records\DMM_Verifications.xls"
>>
>> mSheetname = "we need to work on this"
>>
>> Application.FollowHyperlink _
>> mFilename , mSheetName & "!A1"
>> '~~~~~~~~~~~~~~~~
>>
>> so, now we just have to write the code to calculate the sheetname by
>> referencing the controls that contain the following fields on your
>> form/subforms...
>>
>> TechSurname
>> EquipmentModel
>>
>> where are they located in relationship to where the code is going?
>>
>> 1. subform controlname
>> 2. controlname
>>
>>
>> Warm Regards,
>> Crystal
>>
>> remote programming and training
>>
>> Access Basics
>> 8-part free tutorial that covers essentials in Access
>> http://www.AccessMVP.com/strive4peace
>>
>> *
>> (: have an awesome day
>> *
>>
>>
>>
>>
>> CB wrote:
>>> Hi again,
>>>
>>> My database field names are per your "i.e." below; however, the Excel
>>> worksheets are labelled differently. This was done by someone else to make it
>>> easier to ensure the techs to open the appropriate sheet when they enter
>>> their data. The general format for the sheet labelling is "TechSurname
>>> EquipmentModel".
>>>
>>> When I was first trying to figure this out, I was wondering if would be
>>> helpful to add a field "WorksheetName" to the equipment table. Would this
>>> help??
>>>
>>> Chris
>>>
>>> "strive4peace" wrote:
>>>
>>>> Hi Chris,
>>>>
>>>> how do the sheet names correlate to your data?
>>>> ie:
>>>> 1. EquipmentBrand_fieldname
>>>> 2. _
>>>> 3. SerialNumber_fieldname
>>>>
>>>> if this is right, what are the respective fieldnames? If not, what is
>>>> the correct rule?
>>>>
>>>> Warm Regards,
>>>> Crystal
>>>>
>>>> remote programming and training
>>>>
>>>> Access Basics
>>>> 8-part free tutorial that covers essentials in Access
>>>> http://www.AccessMVP.com/strive4peace
>>>>
>>>> *
>>>> (: have an awesome day
>>>> *
>>>>
>>>>
>>>>
>>>>
>>>> CB wrote:
>>>>> Hi Crystal,
>>>>>
>>>>> The filename is DMM_Verifications.xls and the path is
>>>>> \\ISO\Inspection\Depot\Digital MultiMeter Verification Records.
>>>>>
>>>>> Some sheet names include Jones_179, Smith_87_III, Hamil_87_III
>>>>>
>>>>> Regards,
>>>>> Chris
>>>>>
>>>>> "strive4peace" wrote:
>>>>>
>>>>>> Hi Chris,
>>>>>>
>>>>>> you're welcome
>>>>>>
>>>>>> what is the file name and path?
>>>>>>
>>>>>> what are a few of your sheet names?
>>>>>>
>>>>>> Warm Regards,
>>>>>> Crystal
>>>>>>
>>>>>> remote programming and training
>>>>>>
>>>>>> Access Basics
>>>>>> 8-part free tutorial that covers essentials in Access
>>>>>> http://www.AccessMVP.com/strive4peace
>>>>>>
>>>>>> *
>>>>>> (: have an awesome day
>>>>>> *
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> CB wrote:
>>>>>>> Hi Crystal,
>>>>>>>
>>>>>>> There is only one file. I need to reference different sheets within the one
>>>>>>> file depending on what is displayed for "Equipment" and "Serial Number" in
>>>>>>> the current record (result of a query).
>>>>>>>
>>>>>>> Thanks for the link. I'll check it out.
>>>>>>>
>>>>>>> Chris
>>>>>>>
>>>>>>> "strive4peace" wrote:
>>>>>>>
>>>>>>>> Hi Chris,
>>>>>>>>
>>>>>>>> are the Excel workbooks all in the same location or will the user need a
>>>>>>>> File Open dialog box to browse to a file?
>>>>>>>>
>>>>>>>> "Would I need to use a Select Case"
>>>>>>>>
>>>>>>>> No, it sounds like you can get the filename and sheetname from your data
>>>>>>>> -- just wondering about the path... and you should read this since you
>>>>>>>> will need to use VBA:
>>>>>>>>
>>>>>>>> Access Basics
>>>>>>>> 8-part free tutorial that covers essentials in Access
>>>>>>>> http://www.AccessMVP.com/strive4peace
>>>>>>>>
>>>>>>>> we can help you with the code
>>>>>>>>
>>>>>>>> Warm Regards,
>>>>>>>> Crystal
>>>>>>>>
>>>>>>>>
>>>>>>>> *
>>>>>>>> (: have an awesome day
>>>>>>>> *
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> CB wrote:
>>>>>>>>> Hi all,
>>>>>>>>>
>>>>>>>>> I’m doing some database modification after being away from Access (and the
>>>>>>>>> calibration database I created) for over six years. I’m looking at what I’ve
>>>>>>>>> done and asking myself “How on earth did I do that???” Needless to say, I’m
>>>>>>>>> forgetting an awful lot so please bear with me.
>>>>>>>>>
>>>>>>>>> I’m trying to program a command button such that it will open an Excel
>>>>>>>>> workbook to a specific spreadsheet depending on the equipment and serial
>>>>>>>>> number displayed on the form. (This is only a temporary work around until I
>>>>>>>>> get a chance to set this up in the database but we need something for the ISO
>>>>>>>>> audit coming up.)
>>>>>>>>>
>>>>>>>>> A little background…
>>>>>>>>>
>>>>>>>>> The button is to be on main form whose record source is a query. The main
>>>>>>>>> form displays the query results of equipment make and the serial number. When
>>>>>>>>> scrolling through the records displayed on the main form, the sub-form will
>>>>>>>>> display the associated calibration records for each piece of equipment
>>>>>>>>> (record source is a table).
>>>>>>>>>
>>>>>>>>> My goal …
>>>>>>>>>
>>>>>>>>> When the user scrolls through and stops at the record for the “Fluke 45
>>>>>>>>> DMM”, “serial number 123456”, then clicks the “Get verifications” button, the
>>>>>>>>> workbook will open to the spreadsheet for serial number 123456. If the user
>>>>>>>>> then stops at “Fluke 179 DMM”, “serial number 98765”, then clicks the button,
>>>>>>>>> the workbook will open to the spreadsheet for serial number 98765. And so on.
>>>>>>>>>
>>>>>>>>> I haven’t been able to get a macro to work and am thinking I must use code.
>>>>>>>>> I did find code somewhere on this newsgroup to open a given spreadsheet but
>>>>>>>>> now I need to modify it such that the particular sheet that opens is directly
>>>>>>>>> related to the equipment make and serial number displayed on the main form.
>>>>>>>>>
>>>>>>>>> Would I need to use a Select Case or perhaps incorporate the query that is
>>>>>>>>> the record source for the main form? Am I biting off more than I can chew at
>>>>>>>>> this time?
>>>>>>>>>
>>>>>>>>> Thanks for any and all help!
>>>>>>>>>
>>>>>>>>> Chris
>>>>>>>>>