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

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?

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

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?

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

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

I meant

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 it imports is from a Comm2000 cash register. Every night the computer pulls the sales info from the register to a Data folder-- it's called a .dat file? The excel macros grab the info from the dat file and put it 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 write workbook not sheet!! Sorry about the confusion!!