Guidance required for File Save option through VBA

[milanpatel17]

Hello Guys,

I have created a macro which basically put data from one excel file to main file and than save the main file on desired location.

I was using windows XP previously and the macro was working fine. Below is my problem description.

Windows XP : After starting macro, it collects data from raw data and paste into the main file, than it opens a save as dialogue box, and in the saveas dialogue box, old file name appears automatically so user can edit just a small part and file will be saved. (like 2014 JAN data.xls appears in file name, So I can edit it like 2014 FEB data.xls.)

but in
Windows 7 : when save as dialogue box opens, file name shows blank. and in filetype displays "all files" instead of ".xls" file. So takes more time to write the whole file name in "file name" space.

DO I NEED TO INSTALL ANY ADD ON PACK for this. I am a learning phase on VB Scripting.

Any info will be helpful.

Thanks Guys...

<code i am using>

saven = Application.GetSaveAsFilename
        ActiveWorkbook.SaveAs (saven)

 

[royUK]

You can set the initial file name

Sub SaveAsNewFile()
    Dim sNewName As String, sFileFilter As String, sTitle As String
    Dim vFileSaveName As Variant
    Dim lNewFileFormat As Long
    
    
    If Application.Version >= 12 Then   'Version 12 is xl2007 or newer
      sNewName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & ".xlsm"
      sFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
      lNewFileFormat = 52
    Else
      sNewName = sNewName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 3) & ".xls"
      sFileFilter = "Microsoft Excel Workbook (*.xls), *.xls"
      lNewFileFormat = xlNormal
    End If
    
    sTitle = "Save the workbook to:"
    
    vFileSaveName = Application.GetSaveAsFilename _
            (InitialFileName:=sNewName, _
             FileFilter:=sFileFilter, _
             Title:=sTitle)
    If Not vFileSaveName = False Then
      wb.SaveAs Filename:=vFileSaveName, _
                    FileFormat:=lNewFileFormat
    Else
      MsgBox "File NOT Saved. User cancelled the Save."
    End If


End Sub

 

[milanpatel17]

Hello Sir,

Thank you so much for your reply.

It solved my problem.

 

[royUK]

Glad it helped.