auto run macro on excel start

[whschimmel]

Hello,

I've got this code to get rid of all leading apostrophes in the Excel (2003) Cels:

Sub Auto_Open()
   
    Dim S As Range, temp As String
    Worksheets("qryOfficeNetForeign").Activate
    With Worksheets(1)
    If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
    vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
    Application.ScreenUpdating = False
    For Each S In ActiveSheet.UsedRange
        If S.HasFormula = False Then
             'Gets text and rewrites to same cell without   the apostrophe.
            S.Value = S.Text
        End If
    Next S
    Application.ScreenUpdating = True
End Sub

This code runs fine when I use the shortkey for it (CTRL + R), but when I try to autorun it I get an error:

Runtime Error 91: Object Variable Or With Block Variable Not Set

When I click the debug button, this line is selected:

For Each S In ActiveSheet.UsedRange

How can I autorun this code on opening of Excel, I would also like to run the code only if the sheetname is: "qryOfficeNetForeign"


I am no VBA expert, so am getting only errors


Thanks,
Wouter

 

[Zack Barresse]

This statement is where your error is coming from...

    With Worksheets(1)

You have an opening With but not a corresponding End With, hence error 91.

The Autpen, while it's legacy supported, is not the norm anymore, so I'd recommend using a Workbook_Open procedure. This should work...

Private Sub Workbook_Open()
    Dim S As Range, temp As String, WS As Worksheet
    On Error GoTo ExitRoutine
    Set WS = Me.Worksheets("qryOfficeNetForeign")
    If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
              vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
    Application.ScreenUpdating = False
    For Each S In WS.UsedRange
        If S.HasFormula = False Then
    'Gets text and rewrites to same cell without   the apostrophe.
            S.Value = S.Text
        End If
    Next S
ExitRoutine:
    Application.ScreenUpdating = True
End Sub

HTH

 

[whschimmel]

I tried the code from Zack Barresse but get an error:

Compile Error: Invalid Use of Me Keyword

Also when I open the file it does not run, so I the autorun is not working properly.
I am using Excel 2003, no 2007 or 2010

With my first piece of code it all started automaticly, but get an error:

For Each S In ActiveSheet.UsedRange

--> Looks like the code runs before the sheet is opened, how can I make sure the sheet is opened before the code runs?

Sorry about the one line "With Worksheets(1)", I forgot to remove that one. But still get the error mentioned above when I remove this line.

I placed my code in my Personal macro workbook (Personal.xls), so it can be run on all opened excel sheets

 

[Zack Barresse]

The code was designed to go into a workbooks ThisWorkbook module. If you put it into any other type of module, the Me keyword may not work, as it refers to the module which houses it. Also, it is meant to run on a single workbooks open routine, not on every workbook as it opens. Is this what you're after?

 

[whschimmel]

Zack first of all thanks for placing this code online! Saw your name at that post

Yes I have some digital payment files in XLS format in which the leading apostrophe characters need to be removed.

I just read about the personal macro workbook yesterday, therefore I placed the code/module in there (Personal.xls). I would like:

• 
  Run the code every time excel opens, but only if the (first) sheetname is: "qryOfficeNetForeign"

• 
  If possible to automate the process, only give a message box if an error occurred.

The last point is not necessary, I can also make a script that runs the file and sends an keystroke to the needed window.

 

[Zack Barresse]

Ah, that is different. There are a couple of ways to do this, one way using a class module to capture that type of event (other workbooks opening), and one slightly less robust way without it. You can do this if you want, but it requires some steps to carefully be followed. For more information you can find almost anything you need on this topic here...

http://www.cpearson.com/excel/AppEvent.aspx

For this to work, I'm assuming you're going to be working with your Personal.xls workbook.

In your ThisWorkbook, post this at the top of the module (below any 'Option' statements)...

Private WithEvents XL As Application

In your Personal.xls Workbook_Open procedure, have this line (posting the entire routine, just add the one line of code if you already have one)...

Private Sub Workbook_Open()
    Set XL = Application
End Sub

Then you will have access to multiple workbook events, and you can paste this code in the same module...

Private Sub XL_WorkbookOpen(ByVal Wb As Workbook)
    Dim S As Range, temp As String, WS As Worksheet
    On Error GoTo ExitRoutine
    Set WS = Wb.Worksheets("qryOfficeNetForeign")
    Application.ScreenUpdating = False
    For Each S In WS.UsedRange
        If S.HasFormula = False Then
    'Gets text and rewrites to same cell without   the apostrophe.
            S.Value = S.Text
        End If
    Next S
ExitRoutine:
    Application.ScreenUpdating = True
End Sub

So, if that was all you had in your ThisWorkbook module, the entire module's code would look like this...

Option Explicit

Private WithEvents XL As Application

Private Sub Workbook_Open()
    Set XL = Application
End Sub

Private Sub XL_WorkbookOpen(ByVal Wb As Workbook)
    Dim S As Range, temp As String, WS As Worksheet
    On Error GoTo ExitRoutine
    Set WS = Wb.Worksheets("qryOfficeNetForeign")
    Application.ScreenUpdating = False
    For Each S In WS.UsedRange
        If S.HasFormula = False Then
    'Gets text and rewrites to same cell without   the apostrophe.
            S.Value = S.Text
        End If
    Next S
ExitRoutine:
    Application.ScreenUpdating = True
End Sub

This is the non-class method of doing it. You will need to save and close Excel and re-open to get it to work (or put your cursor in the Workbook_Open procedure and press F5). Remember, any changes made to that module will break the connection to the "XL" variable and you will either need to close/re-open Excel or re-run the open procedure manually, in order to get it to work again.

Let us know if this works for you.