Using an auto open macro to create a replacement auto open macro 
Author Message
 Using an auto open macro to create a replacement auto open macro

I have posted questions that I thought were impossible and usually
someone proves me wrong.  Here is another one.

I have a spreadsheet which I run just once a day.  This workbook has
an auto open macro that does the following:

Brings in data from a text file created overnight
Deletes unnecessary columns
Formats the information in a number of ways
Saves the workbook under a different name, replacing yesterdays data
file
Closes the file

I then call up the file for reference throughout the day as necessary.

My problem is that I would like to have a macro that runs when I open
this new file to automate it to a certain extent e.g. automatically
filter based on predefined criteria.  Is there any way I can add such
an auto open macro to the new file I create each day.  

Thanks

David Prout
Anti Spam Signature - replace .net with .com



Fri, 09 Sep 2005 20:41:05 GMT  
 Using an auto open macro to create a replacement auto open macro
I think this will work:

Option Explicit
Sub auto_open()

    Dim myName As Name
    Dim AlReadyRun As Boolean

    On Error Resume Next
    Set myName = ThisWorkbook.Names("AlReadyRun")
    On Error GoTo 0

    AlReadyRun = False
    If myName Is Nothing Then
        'do nothing
    ElseIf Evaluate(myName.Value) <> CLng(Date) Then
        'do nothing
    Else
        AlReadyRun = True
    End If

    If AlReadyRun Then
        'do nothing
    Else
        'run your code and then update the name
        ThisWorkbook.Names.Add Name:="alreadyrun", _
                RefersTo:=CLng(Date), Visible:=True
    End If

End Sub

But I think I'd create another workbook that actually held just the code to do
the import.  Then that code could actually create a new workbook--not in a
worksheet in the same workbook.

If you actually have code that needs to be kept, then I think I'd make a
template workbook that contains all the "common" code (not the import code).
Then the import code could import the text file, and copy it to a new file based
on that "template" workbook.  Then save that one.

Quote:

> I have posted questions that I thought were impossible and usually
> someone proves me wrong.  Here is another one.

> I have a spreadsheet which I run just once a day.  This workbook has
> an auto open macro that does the following:

> Brings in data from a text file created overnight
> Deletes unnecessary columns
> Formats the information in a number of ways
> Saves the workbook under a different name, replacing yesterdays data
> file
> Closes the file

> I then call up the file for reference throughout the day as necessary.

> My problem is that I would like to have a macro that runs when I open
> this new file to automate it to a certain extent e.g. automatically
> filter based on predefined criteria.  Is there any way I can add such
> an auto open macro to the new file I create each day.

> Thanks

> David Prout
> Anti Spam Signature - replace .net with .com

--

Dave Peterson



Sat, 10 Sep 2005 05:03:26 GMT  
 Using an auto open macro to create a replacement auto open macro
I meant to make the .visible = false (but wanted to see it test mode).

Quote:

> I think this will work:

> Option Explicit
> Sub auto_open()

>     Dim myName As Name
>     Dim AlReadyRun As Boolean

>     On Error Resume Next
>     Set myName = ThisWorkbook.Names("AlReadyRun")
>     On Error GoTo 0

>     AlReadyRun = False
>     If myName Is Nothing Then
>         'do nothing
>     ElseIf Evaluate(myName.Value) <> CLng(Date) Then
>         'do nothing
>     Else
>         AlReadyRun = True
>     End If

>     If AlReadyRun Then
>         'do nothing
>     Else
>         'run your code and then update the name
>         ThisWorkbook.Names.Add Name:="alreadyrun", _
>                 RefersTo:=CLng(Date), Visible:=True
>     End If

> End Sub

> But I think I'd create another workbook that actually held just the code to do
> the import.  Then that code could actually create a new workbook--not in a
> worksheet in the same workbook.

> If you actually have code that needs to be kept, then I think I'd make a
> template workbook that contains all the "common" code (not the import code).
> Then the import code could import the text file, and copy it to a new file based
> on that "template" workbook.  Then save that one.


> > I have posted questions that I thought were impossible and usually
> > someone proves me wrong.  Here is another one.

> > I have a spreadsheet which I run just once a day.  This workbook has
> > an auto open macro that does the following:

> > Brings in data from a text file created overnight
> > Deletes unnecessary columns
> > Formats the information in a number of ways
> > Saves the workbook under a different name, replacing yesterdays data
> > file
> > Closes the file

> > I then call up the file for reference throughout the day as necessary.

> > My problem is that I would like to have a macro that runs when I open
> > this new file to automate it to a certain extent e.g. automatically
> > filter based on predefined criteria.  Is there any way I can add such
> > an auto open macro to the new file I create each day.

> > Thanks

> > David Prout
> > Anti Spam Signature - replace .net with .com

> --

> Dave Peterson


--

Dave Peterson



Sat, 10 Sep 2005 05:22:11 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Auto Open Macro

2. auto-open macro that starts with Excel

3. Disabling Auto Open Macros - is it possible?

4. Auto open macros

5. macro auto enable when workbook opened

6. Auto Open + close Macro's

7. Auto execute a macro when the workbook opens

8. Updatefromfile and auto-open-macro's

9. auto open excel macro

10. Templates & auto open macro


 
Powered by phpBB® Forum Software © phpBB Group