Need to run a macro on opening a new excel file

[sdinesh86]

Hello,

What i have :

I have to download a sheet generated from a website, i have a automation that does that for me and opens the excel file.

Now when the file is open i get a warning that the file is in different format, if click yes the file opens correctly.

i have a macro defined in personal.xls file which picks up range of cell and formats it and copy's it to the clipboard and also it fires up function that creates a outlook mail.

My Need :

1. i want a way to by pass or accept the warning message that appears while opening the downloaded file.

2. once the file is opened, i want to run the macro defined in personal.xls file on to the file thats opened.

is there a way i can do it.
Thanks in advance.

 

[RichardMGreen]

To get rid of the warnings try application.displayalerts=false
Don't forget to put it back to true once the file is open though.

I'll have a look at the second part (running the macro) when I get the chance.
Can you post your code in the meantime?

 

[sdinesh86]

Private Sub Workbook_Open()

Prepare_Table

End Sub

Sub Prepare_Table()
Dim msgInput As Integer
Dim olApp As Outlook.Application
If prepareTable = True Then
    msgInput = MsgBox("Table Created do you want to open Outlook and send mail", vbYesNo)
        frwdMail
    If msgInput = vbYes Then
        
    End If
Else
    MsgBox "No Table created", vbOKOnly
End If

End Sub

Function prepareTable() As Boolean

findCells

Dim typeSelection As String

If TypeName(Selection) = "" Then
    
    MsgBox "There is nothing selected, First make the selection and then Run the Macro", vbOKOnly
    prepareTable = False
    
Else
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Style = "Normal"
    Selection.UnMerge
    Range("C1").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Hidden = True
    Range("C36").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Range("A1:C1").Select
    Columns("C:C").EntireColumn.AutoFit
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Range("A:A,B:B").Select
    Range("B1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
    Selection.Copy
    
    prepareTable = True
    
End If

End Function

Function frwdMail()
    reportForm.Show
End Function

Function findCells()

Dim keyC, SeverityC, SummaryC As String
Dim keyR, Severity, Summary As String
Dim KeyRange, SeverityRange, SummaryRange As String
Dim lastRowNumber As Long

lastRowNumber = [A:C].Find(What:="*", After:=[C1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Cells.Find(What:="key", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
   keyC = Selection.Address
keyR = colLetter(ActiveCell.Column)

Cells.Find(What:="Severity", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
   SeverityC = Selection.Address
SeverityR = colLetter(ActiveCell.Column)

Cells.Find(What:="Summary", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
   SummaryC = Selection.Address
SummaryR = colLetter(ActiveCell.Column)

KeyRange = keyC & ":" & keyR & lastRowNumber
SeverityRange = SeverityC & ":" & SeverityR & lastRowNumber
SummaryRange = SummaryC & ":" & SummaryR & lastRowNumber

MsgBox KeyRange & ":" & SeverityRange & ":" & SummaryRange, vbOKOnly

Range(KeyRange & "," & SeverityRange & ":" & SummaryRange).Select
''Range(SeverityC & ":" & SeverityR & lastRowNumber).Select
''Range(SummaryC & ":" & SummaryR & lastRowNumber).Select

End Function

Function colLetter(col As Long)
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
colLetter = sColumn
End Function

This the code i have in the personal.xls file which opens when excel starts.

* Also i tried the application.displayalerts = false in the personal.xls but it doesnt work.

when the file gets downloaded and executed, personal.xls starts first (i have a workbook_open which gets executed and throws a messaged letting me know its open ) and then i get the Warning, if i click yes then the file opens up

 

[RichardMGreen]

Not sure about the warning message then, maybe someone else does?
As for the macro, you'd need to point it at the newly opened workbook before running the rest. Is the workbook name always the same?

 

[sdinesh86]

Yes the name is always the same.

but some times if there are files in the folder then the name changes, but i can write function to get the list of files in the folder and open the correct one.

 

[RichardMGreen]

If the filename is always the same, then you just need to activate it before running any more of the code.
I'm still looking into the warning message, but I'm not having too much luck.

 

[sdinesh86]

It seems that the application.displayalerts doesnt work and gets cancelled if a new workbook is opened.