How do I change the ICONS of my Excel Files which are created using VBA from a Single WorkBook ?

[all4excel]

Dear Board,

Using some VBA code which I got from the Net, I am creating a New File/WorkBook from an Original File which has some sheets which are like Value Copies of the Original File for Distribution..

Now the problem I face is that these files are getting created in the format shown in the last two files..

Now everytime I have to open these files using the Open With Dialog Box though these are normal files with no VBA code or even any kind of Macros in it..

So how do I repair this error ?

Please find the code below which I am using to create these files from my Sheets...

Public Sub MoveData()
    Dim aScriptObject As Object, c, bScriptObject As Object
    Dim ws As Worksheet, nBook As Workbook
    Dim Nm As Name                                         
    Dim NewWkBkName As String
    Dim FolderName As String
    Dim WkBkPath As String
    Dim NewWkBkFolder As String
    Dim ShtCount
    WkBkPath = Sheets("Insurance").Range("AC2").Value
    NewWkBkFolder = Sheets("Insurance").Range("AC4").Value
    NewWkBkName = Sheets("Insurance").Range("AC5").Value


    Set aScriptObject = CreateObject("Scripting.FileSystemObject")
    If Not aScriptObject.FolderExists(WkBkPath & NewWkBkFolder) Then
        Set c = aScriptObject.CreateFolder(WkBkPath & NewWkBkFolder)
    End If
    Set bScriptObject = CreateObject("Scripting.FileSystemObject")
    If bScriptObject.FileExists(WkBkPath & NewWkBkFolder & "\" & NewWkBkName & ".xlsm") Then
        MsgBox "The File Name exists"
        Exit Sub
    End If


    For Each ws In ThisWorkbook.Worksheets
          
        If ws.Name Like "A* Book" Or ws.Name Like "B* Look"  Then
            ShtCount = ShtCount + 1
            If ShtCount = 1 Then
                Set nBook = Workbooks.Add
                nBook.SaveAs WkBkPath & NewWkBkFolder & "\" & NewWkBkName, FileFormat:=xlOpenXMLWorkbookMacroEnabled    'xlNormal
            End If


            With ThisWorkbook
                .Sheets(ws.Name).Copy Before:=Workbooks(NewWkBkName).Sheets(1)
                Call DeleteCode


            End With
        End If
    Next
        For Each ws In Worksheets
                If ws.Name Like "Sheet*" Then ws.Delete
                Application.DisplayAlerts = False
        Next ws
        
        For Each Nm In Workbooks(NewWkBkName).Names
                Nm.Delete
        Next Nm
        
Application.DisplayAlerts = False
Workbooks(NewWkBkName).Close True
'Application.DisplayAlerts = True




End Sub

Can someone please guide me on the same this code which I am using is not my own but I am working around it..

Thanks in advance

Warm Regards
all4excel

 

[RoryA]

The picture you posted shows two Word documents, not Excel files?

 

[all4excel]

Yes that's the Image I found on the net...so i chose to paste it however the same 2 icons on the left are shown for my excel files.


Before putting this query I came across this link on the net and found these Image Icons

http://superuser.com/questions/505859/microsoft-word-files-have-weird-icons-suddenly

But since I am creating my own Excel Files using code my query is different than the one mentioned i the website

 

[RoryA]

The displayed icons are down to Windows and based purely on the file extension. If you aren't seeing icons there is something wrong with your Windows settings.

 

[all4excel]

But out of the four different FIles i'm creating using this code only three appear differently and one appears as normal Macro Enabled Excel File

 

[RoryA]

Are all of them .xlsm files? (it's still down to Windows what icons are shown - you can't show a different one for one file)

 

[all4excel]

Yes Absolutely I generate 4 Different Files from one single WOrkbook and one file comes out with a normal Icon and the remaining three are coming in the Icons shown earlier...

Now if there was a Windows Issue then all Excel Files need to be different which is not the case and only these files are in different ICons...

I am able to open these files by everytime Choosing the Open With Excel with the change to this option disabled by default...

SO any ideas what I need to do, the Main file which generates these files is a Macro Enabled File with command buttons and Macros...but the files generated from this is a Copy of the Sheets in the Main file which are copied and then these are moved into a New WOrkbook and this is saved as a New File...

 

[Cindy Ellis]

Try changing this line in your macro

[COLOR=#333333]nBook.SaveAs WkBkPath & NewWkBkFolder & "\" & NewWkBkName, FileFormat:=xlOpenXMLWorkbookMacroEnabled    'xlNormal[/COLOR]

to

[COLOR=#333333]nBook.SaveAs WkBkPath & NewWkBkFolder & "\" & NewWkBkName[/COLOR][B][COLOR=#ff0000] & ".xlsm"[/COLOR][/B][COLOR=#333333], FileFormat:=xlOpenXMLWorkbookMacroEnabled 'xlNormal[/COLOR]

Hope that helps,

 

[all4excel]

Thanks a lot Cindy this is exactly what i needed...Now lets say if i had to save these new files without the Macro then what changes do i need to make..

Well it does not make a difference as now I got what i was looking out for however these new files are just value sheets without any macro so it will be more apt to have them without a Macro and also if possible to save them as 97-2003 incase if they have to be distributed to people with lesser versions..

Thanks a lot...

 

[all4excel]

I changed the ".xlsm" to "xls" and it worked but I need to understand how do i mkae it change to 97-2003 and also just after changing the ".xls" it worked so how does the sentence "FileFormat:=xlOpenXMLWorkbookMacroEnabled 'xlNormal" work then ?