Hi all,
I am including my code below although it doesn't really relate to my problem other than the fact that I am trying to disable a Windows Warning Message that comes up when I attempt to run this code. I found the solution on Microsoft's website
http://support.microsoft.com/kb/829072
Method 1: Turn off the "Confirm open after download" option for the file type that you are trying to open
To do this, follow these steps:
1. Double-click My Computer.
2. On the Tools menu, click Folder Options.
3. On the File Types tab, select the appropriate file extension (for example, WMV) in the Registered File Types box, then click the Advanced button.
4. Click to clear the Confirm open after download check box, then click the OK button.
5. Click the Close button on the Folder Options dialog box.
I have already modified this warning on my computer but I have to open up this workbook to a broader audience and I don't want each of them to edit their settings manual. Does anyone know the way to do this programmically. I think this is a little out of my league at this point. So the macro would cycle thru the above protocol and accept .xls, .csv files. Any ideas?
Application.DisplayAlerts = False
'Date Set up for File Naming
Dim newDate
Dim saveDate As String
newDate = Range("k2").Value
saveDate = Format(newDate, "mm-dd-yy")
'Define the Lastrow in the Links Workbook
Cells(65536, 2).End(xlUp).Offset(0, 0).Select
lastwbrow = ActiveCell.Row
Workbooks.Add
Set Workbook = ActiveWorkbook
Windows("LinksDemo.xls").Activate
'Cycle thru remaining reports
For i = 4 To lastwbrow
Windows("LinksDemo.xls").Activate
Group = Cells(i - 1, 4).Text
tree = Cells(i - 1, 3).Text
'Close out a workbook with Different Workbook Name
If Cells(i, 4) <> Cells(i - 1, 4) Then
Workbook.Activate
ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & " - " & tree & "_" & Group & "_" & saveDate
ActiveWorkbook.Close
Workbooks.Add
Set Workbook = ActiveWorkbook
End If
Windows("LinksDemo.xls").Activate
Cells(i, 2).Activate
Reportname = ActiveCell.Text
Group = Cells(i - 1, 4).Text
Selection.Hyperlinks(1).Follow NewWindow:=True, AddHistory:=False
Set DownloadGP = ActiveWorkbook
DownloadGP.Activate
Cells.Select
Selection.Copy
Workbook.Activate
Sheets.Add
ActiveSheet.Paste
ActiveSheet.name = Left(Reportname, 30)
DownloadGP.Close False
Windows("LinksDemo.xls").Activate
Next i
Windows("LinksDemo.xls").Activate
Cells(65536, 2).End(xlUp).Offset(0, 0).Select
Set lastrow = ActiveCell
Group = lastrow.Offset(0, 2).Text
tree = lastrow.Offset(0, 1).Text
Workbook.Activate
ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & " - " & tree & "_" & Group & "_" & saveDate
ActiveWorkbook.Close
I am including my code below although it doesn't really relate to my problem other than the fact that I am trying to disable a Windows Warning Message that comes up when I attempt to run this code. I found the solution on Microsoft's website
http://support.microsoft.com/kb/829072
Method 1: Turn off the "Confirm open after download" option for the file type that you are trying to open
To do this, follow these steps:
1. Double-click My Computer.
2. On the Tools menu, click Folder Options.
3. On the File Types tab, select the appropriate file extension (for example, WMV) in the Registered File Types box, then click the Advanced button.
4. Click to clear the Confirm open after download check box, then click the OK button.
5. Click the Close button on the Folder Options dialog box.
I have already modified this warning on my computer but I have to open up this workbook to a broader audience and I don't want each of them to edit their settings manual. Does anyone know the way to do this programmically. I think this is a little out of my league at this point. So the macro would cycle thru the above protocol and accept .xls, .csv files. Any ideas?
Application.DisplayAlerts = False
'Date Set up for File Naming
Dim newDate
Dim saveDate As String
newDate = Range("k2").Value
saveDate = Format(newDate, "mm-dd-yy")
'Define the Lastrow in the Links Workbook
Cells(65536, 2).End(xlUp).Offset(0, 0).Select
lastwbrow = ActiveCell.Row
Workbooks.Add
Set Workbook = ActiveWorkbook
Windows("LinksDemo.xls").Activate
'Cycle thru remaining reports
For i = 4 To lastwbrow
Windows("LinksDemo.xls").Activate
Group = Cells(i - 1, 4).Text
tree = Cells(i - 1, 3).Text
'Close out a workbook with Different Workbook Name
If Cells(i, 4) <> Cells(i - 1, 4) Then
Workbook.Activate
ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & " - " & tree & "_" & Group & "_" & saveDate
ActiveWorkbook.Close
Workbooks.Add
Set Workbook = ActiveWorkbook
End If
Windows("LinksDemo.xls").Activate
Cells(i, 2).Activate
Reportname = ActiveCell.Text
Group = Cells(i - 1, 4).Text
Selection.Hyperlinks(1).Follow NewWindow:=True, AddHistory:=False
Set DownloadGP = ActiveWorkbook
DownloadGP.Activate
Cells.Select
Selection.Copy
Workbook.Activate
Sheets.Add
ActiveSheet.Paste
ActiveSheet.name = Left(Reportname, 30)
DownloadGP.Close False
Windows("LinksDemo.xls").Activate
Next i
Windows("LinksDemo.xls").Activate
Cells(65536, 2).End(xlUp).Offset(0, 0).Select
Set lastrow = ActiveCell
Group = lastrow.Offset(0, 2).Text
tree = lastrow.Offset(0, 1).Text
Workbook.Activate
ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & " - " & tree & "_" & Group & "_" & saveDate
ActiveWorkbook.Close
