detecting open WB at start up

[Eugeneb]

Hi

I am not that familiar with VB, so need some help with Excel

I have a CSV file that needs formatting, I can do this, but also need to add some code to the personal.xls workbook so that if I open 'test.csv' the macro is run to format the file etc. But if another file opens don't run formatting macro

Any Ideas?

Eugene

[Bob Phillips]

Option Explicit

Private Const Filename As String = _
"C:\personal\Bob\myFile.xls" '<=== change to suit
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If Wb.FullName = Filename Then
Call myMacro '<=== change to suit
End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Eugeneb]

Thanks for the code,

I have opened Excel and pasted the code into my 'personal.xls' workbook

I changed the code to point to my file and the macro I want to run but when I double click on the file to open it, the macro isn't run.

I have tested my macro by running it manually.

Any Ideas?


Option Explicit

Private Const Filename As String = _
"C:\aaa\test.csv" '<=== change to suit
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If Wb.FullName = Filename Then
Call FormatandPrint '<=== change to suit
End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

Sub FormatandPrint()
On Error GoTo ErrorErr
Cells.Select
Selection.Columns.AutoFit
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Whangarei District Council" & Chr(10) & "Callout Report"
.RightHeader = "&D&T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Once Saved Close Workbook'
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = False
'Application.Quit

Exit Sub

ErrorErr:
Resume Next
End Sub


Edited 10-Apr-07 by geekgirlau. Reason: insert vba tags

[Bob Phillips]

Did you put it in the ThisWorkbook code module as described?

[Eugeneb]

Yeah into the Personal.xls, This Workbook

[Bob Phillips]

Post your wotkbook.

[Eugeneb]

Heres My Workbook, it normally sits in XLStart Dir so everytime Excel Starts this WB is opened.

[Bob Phillips]

It worked perfectly for me.

[Eugeneb]

Bum not whatI wanted to here!

What would stop it working on mine?

If I just fire up Excel and view the VB, the WB is open and the macros are there, my security is set to low....