|
VBA Code help
Every two weeks I have a Business Objects query set on a scheduler to
refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! |
VBA Code help
In your vLookup workbook, change all of your lookup formulas to
reference a single cell. That cell will have the most recent workbook name/path in it. In the vLookup workbook open event, add code to search the folder containing the Sick workbooks and return the most recent file. Have the code add that file name/path to the cell established above. John Walkenbach has some code to find the latest file in a folder here... http://j-walk.com/ss/excel/tips/tip97.htm Personally, I prefer file search code using the File System Object. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "TimN" wrote in message Every two weeks I have a Business Objects query set on a scheduler to refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! |
VBA Code help
Evening Tim,
I am not sure of the easiest way to re-reference your spreadsheets, BUT... Why don't you have the reference set to SICK_CURRENT and have your scheduler create 2 copies, one with the date and the other called SICK_CURRENT? HTH Simon TimN wrote: Every two weeks I have a Business Objects query set on a scheduler to refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
VBA Code help
Great Minds think alike! :O
Gary Brown "smw226 via OfficeKB.com" wrote: Evening Tim, I am not sure of the easiest way to re-reference your spreadsheets, BUT... Why don't you have the reference set to SICK_CURRENT and have your scheduler create 2 copies, one with the date and the other called SICK_CURRENT? HTH Simon TimN wrote: Every two weeks I have a Business Objects query set on a scheduler to refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
VBA Code help
Simon,
Thanks for the suggestion. let me toss out one more thing just for ideas on how to handle this. I like the idea of linking to SICK_Current.xls. That keeps the most current data attached to the file via the VLOOKUP formulas. The problem I still face is when someone has to go back to a prior period to make a correction to the sick time calculation, they would need to have the sick data as of that payroll period (a period from past history, not the current period's data), i.e SICK_10_15_06.xls for example. Any suggestions on how to overcome that issue? Thanks for any thoughts. Tim "smw226 via OfficeKB.com" wrote: Evening Tim, I am not sure of the easiest way to re-reference your spreadsheets, BUT... Why don't you have the reference set to SICK_CURRENT and have your scheduler create 2 copies, one with the date and the other called SICK_CURRENT? HTH Simon TimN wrote: Every two weeks I have a Business Objects query set on a scheduler to refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
VBA Code help
Re-save 'SICK_10_15_06.xls' as 'SICK_Current.xls'.
-- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "TimN" wrote: Simon, Thanks for the suggestion. let me toss out one more thing just for ideas on how to handle this. I like the idea of linking to SICK_Current.xls. That keeps the most current data attached to the file via the VLOOKUP formulas. The problem I still face is when someone has to go back to a prior period to make a correction to the sick time calculation, they would need to have the sick data as of that payroll period (a period from past history, not the current period's data), i.e SICK_10_15_06.xls for example. Any suggestions on how to overcome that issue? Thanks for any thoughts. Tim "smw226 via OfficeKB.com" wrote: Evening Tim, I am not sure of the easiest way to re-reference your spreadsheets, BUT... Why don't you have the reference set to SICK_CURRENT and have your scheduler create 2 copies, one with the date and the other called SICK_CURRENT? HTH Simon TimN wrote: Every two weeks I have a Business Objects query set on a scheduler to refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
VBA Code help
That certainly would work. I was trying to come up with a more systematic
way to do it so the group of users who are not too sophisticated, won't have to go in and manipulate the data files. TKN "Gary Brown" wrote: Re-save 'SICK_10_15_06.xls' as 'SICK_Current.xls'. -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "TimN" wrote: Simon, Thanks for the suggestion. let me toss out one more thing just for ideas on how to handle this. I like the idea of linking to SICK_Current.xls. That keeps the most current data attached to the file via the VLOOKUP formulas. The problem I still face is when someone has to go back to a prior period to make a correction to the sick time calculation, they would need to have the sick data as of that payroll period (a period from past history, not the current period's data), i.e SICK_10_15_06.xls for example. Any suggestions on how to overcome that issue? Thanks for any thoughts. Tim "smw226 via OfficeKB.com" wrote: Evening Tim, I am not sure of the easiest way to re-reference your spreadsheets, BUT... Why don't you have the reference set to SICK_CURRENT and have your scheduler create 2 copies, one with the date and the other called SICK_CURRENT? HTH Simon TimN wrote: Every two weeks I have a Business Objects query set on a scheduler to refresh. It then saves to an Excel file in a defined location named like SICK_10_15_06.xls. That file contains the most current list of employees and their available sick hours. Every 2 weeks this query refreshes and saves a new file with the most current data such as SICK_10_29_06.xls. I have in addition another excel file that has multiple VLOOKUP formulas that link to the above mentioned file in order to pull in the employee name and sick hours available. My question is how can I get my VLOOKUP formula to link to the most current excel file? Initially it would be linked to SICK_10_15_06.xls, but when SICK_10_29_06.xls is created, I want my VLOOKUP to switch to that file. Any ideas on how to get there? Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
VBA Code help
Hi Tim,
What I am thinking is.... auto_exec macro on the file which scans a given folder and lists all files on a hidden tab. Combo Box driven by this list. VLookups driven by value of Combo List That said, could this data be put into access as this would solve all your issues, allow you to keep an audit log of people's actions and , if programmed correctly, allow you to easilly restore data if changes are made in error. One thing I have learned, the hard way, is you want to give the minimum of people access to amend your data and, where you do, you want to keep detailed audit trails of thier actions. HTH Simoin TimN wrote: That certainly would work. I was trying to come up with a more systematic way to do it so the group of users who are not too sophisticated, won't have to go in and manipulate the data files. TKN Re-save 'SICK_10_15_06.xls' as 'SICK_Current.xls'. [quoted text clipped - 41 lines] Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
VBA Code help
Is it not just easier to link your lookup tables into BO so that they are
there at source? "smw226 via OfficeKB.com" wrote: Hi Tim, What I am thinking is.... auto_exec macro on the file which scans a given folder and lists all files on a hidden tab. Combo Box driven by this list. VLookups driven by value of Combo List That said, could this data be put into access as this would solve all your issues, allow you to keep an audit log of people's actions and , if programmed correctly, allow you to easilly restore data if changes are made in error. One thing I have learned, the hard way, is you want to give the minimum of people access to amend your data and, where you do, you want to keep detailed audit trails of thier actions. HTH Simoin TimN wrote: That certainly would work. I was trying to come up with a more systematic way to do it so the group of users who are not too sophisticated, won't have to go in and manipulate the data files. TKN Re-save 'SICK_10_15_06.xls' as 'SICK_Current.xls'. [quoted text clipped - 41 lines] Thanks! -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
| All times are GMT +1. The time now is 08:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com