+ Reply to Thread
Results 1 to 9 of 9

Open workbook macro- find correct month to open?

  1. #1
    Registered User
    Join Date
    06-29-2006
    Posts
    15

    Open workbook macro- find correct month to open?

    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.

  2. #2
    Jim Jackson
    Guest

    RE: 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. #3
    Registered User
    Join Date
    06-29-2006
    Posts
    15
    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

  4. #4
    Jim Jackson
    Guest

    Re: 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. #5
    Registered User
    Join Date
    06-29-2006
    Posts
    15
    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?

  6. #6
    Jim Jackson
    Guest

    Re: 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. #7
    Registered User
    Join Date
    06-29-2006
    Posts
    15
    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?
    Please Login or Register  to view this content.
    Do you think this would work okay or not? Thanks again for your help!!- Mike

  8. #8
    Jim Jackson
    Guest

    Re: 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. #9
    Registered User
    Join Date
    06-29-2006
    Posts
    15
    I meant
    Please Login or Register  to view this content.
    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!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1