Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
So I have Monthly sales sheets that import my cash register data int them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs th macro to import the data into the correct day of the month. Here is th workbook open macro- Code ------------------- Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime = TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub[\code] This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date? Thanks so much -- buzzharle ----------------------------------------------------------------------- buzzharley's Profile: http://www.excelforum.com/member.php...fo&userid=3588 View this thread: http://www.excelforum.com/showthread.php?threadid=55947 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
If DatePart("m", Date) = "1" Then
Sheets("Jan").select ' or however you have named the January sheet elseif DatePart("m",Date) = "2" then Sheets("Feb").select 'through the twelve months end if Best wishes, Jim "buzzharley" wrote: So I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook open macro- Code: -------------------- Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime = TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub[\code] This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date? Thanks so much. -- buzzharley ------------------------------------------------------------------------ buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886 View this thread: http://www.excelforum.com/showthread...hreadid=559474 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
Thanks so much for your help Jim- Where should I put it in the macro? I assume it would be in the first macro? Workbook_Open() If DatePart("m", Date) = "1" Then Sheets("Jan").select ' or however you have named the January sheet elseif DatePart("m",Date) = "2" then Sheets("Feb").select 'through the twelve months Dim dTime As Date dTime = Time If dTime = TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub -- buzzharley ------------------------------------------------------------------------ buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886 View this thread: http://www.excelforum.com/showthread...hreadid=559474 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
Is "ImportData" the name of the second macro? If so, I would place it there
at the first. After the macro has determined and selected the proper worksheet, it can then locate the correct cell(s) for inserting the new data. I am heading home from work but will check back as soon as I can so I can be sure I am really helping. Jim "buzzharley" wrote: Thanks so much for your help Jim- Where should I put it in the macro? I assume it would be in the first macro? Workbook_Open() If DatePart("m", Date) = "1" Then Sheets("Jan").select ' or however you have named the January sheet elseif DatePart("m",Date) = "2" then Sheets("Feb").select 'through the twelve months Dim dTime As Date dTime = Time If dTime = TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub -- buzzharley ------------------------------------------------------------------------ buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886 View this thread: http://www.excelforum.com/showthread...hreadid=559474 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
Yes the second macro is the import data macro, the first being th button macro. I just realized that I will have to mess with it ever month anyways due to task manager only opening up the one mont workbook unless I tell it to open all of the workbooks -- buzzharle ----------------------------------------------------------------------- buzzharley's Profile: http://www.excelforum.com/member.php...fo&userid=3588 View this thread: http://www.excelforum.com/showthread.php?threadid=55947 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
Buzz,
I have realized that I gave the wrong code sequence for what you need. Let me be sure I understand now. You have a separate workbook for each month and each workbook has separate sheets for each reporting date? If this is the case then the ImportData macro would start like this: If DatePart("m", Date) = "1" Then Workbooks("Jan.xls").open ' or however you have named the January workbook elseif DatePart("m",Date) = "2" then Workbooks("Feb.xls").open 'through the twelve months end if Depending on the source of the data, you might be able to reference the date on the document for determining the sheet to select. If you will give me some more exact info about that part I will try to help work that out. Jim "buzzharley" wrote: Yes the second macro is the import data macro, the first being the button macro. I just realized that I will have to mess with it every month anyways due to task manager only opening up the one month workbook unless I tell it to open all of the workbooks? -- buzzharley ------------------------------------------------------------------------ buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886 View this thread: http://www.excelforum.com/showthread...hreadid=559474 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
Yes Jim that is correct. There is a July workbook, aug workbook and s on!! So if I have a open workbook macro on them all then it won't kno which one to open. A person suggested on me using a Scheduling workboo that opens up using task manager- In that scheduling workbook there is openworkbook macro that tell it what month workbook to open? Code ------------------- Private Sub Workbook_Open() If DatePart("m", Date) = "1" Then Sheets("2006-Jan-Sales").select ElseIf DatePart("m",Date) = "2" Then Sheets("2006-Feb-Sales").select 'through the twelve months End If End Sub ------------------- Do you think this would work okay or not? Thanks again for you help!!- Mik -- buzzharle ----------------------------------------------------------------------- buzzharley's Profile: http://www.excelforum.com/member.php...fo&userid=3588 View this thread: http://www.excelforum.com/showthread.php?threadid=55947 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
That will work if you are looking to open specific sheets in the same
workbook (one sheet for January, etc). But if there is a separate workbook for January then the second example I posted will work to open only the workbook that is needed. How are the sheets labeled in the workbooks? From what kind of document is the data being imported? This information will give a better handle on how to help. Thanks, Jim "buzzharley" wrote: Yes Jim that is correct. There is a July workbook, aug workbook and so on!! So if I have a open workbook macro on them all then it won't know which one to open. A person suggested on me using a Scheduling workbook that opens up using task manager- In that scheduling workbook there is a openworkbook macro that tell it what month workbook to open? Code: -------------------- Private Sub Workbook_Open() If DatePart("m", Date) = "1" Then Sheets("2006-Jan-Sales").select ElseIf DatePart("m",Date) = "2" Then Sheets("2006-Feb-Sales").select 'through the twelve months End If End Sub -------------------- Do you think this would work okay or not? Thanks again for your help!!- Mike -- buzzharley ------------------------------------------------------------------------ buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886 View this thread: http://www.excelforum.com/showthread...hreadid=559474 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbook macro- find correct month to open?
I meant Code ------------------- Private Sub Workbook_Open() If DatePart("m", Date) = "1" Then Workbook("2006-Jan-Sales").select ElseIf DatePart("m",Date) = "2" Then Workbook("2006-Feb-Sales").select 'through the twelve months End If End Sub ------------------- The file is way to big or else I would upload it here. The data i imports is from a Comm2000 cash register. Every night the compute pulls the sales info from the register to a Data folder-- it's called .dat file? The excel macros grab the info from the dat file and put i into the right cells like- visa, mastercard, pizza sales, pasta sales. Each month is a new workbook. So in the example above I meant to writ workbook not sheet!! Sorry about the confusion! -- buzzharle ----------------------------------------------------------------------- buzzharley's Profile: http://www.excelforum.com/member.php...fo&userid=3588 View this thread: http://www.excelforum.com/showthread.php?threadid=55947 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
How to find file-path of open workbook? | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Find and Open Workbook then copy and move sheet | Excel Programming | |||
How to find if a workbook is open | Excel Programming |