Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

To Show Both .xls And .xlsx Files While Opening

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have created a macro that opens other microsoft excel spreadsheet files. I want that when the user browse for the open file then the window that opens shud display both .xls and xlsx files at once. the code i ve used is below:

Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
Finfo = "Workbook (*.xls),*.xls," & "Office 2007 Excel Workbook (*.xlsx),*.xlsx,"
' Display *.* by default
FilterIndex = 1
' Set the dialog box caption
Title = "Select a File to Import"
' Get the filename
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)
' Handle return info from dialog box
If FileName = False Then
MsgBox "No file was selected."
'MsgBox "You selected " & FileName
' opens an the excel file at the location: FileName
Workbooks.Open (FileName)

View Answers     

Similar Excel Tutorials

Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Reference Other Excel Files with Formulas and Functions
In Excel you can use formulas and functions to reference data that is stored in another Excel workbook.  This creat ...
Combine Worksheets from Multiple Workbooks into One
Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined in ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...

Helpful Excel Macros

Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Open a PowerPoint Presentation from Excel
- This free macro for Microsoft Excel allows you to open any PowerPoint presentation from excel. You can change the file
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

Similar Topics

Hello All,

I am able to open a .txt file in a workbook using ths code below but my problem now is that the .txt file is delimited (has commas). Anyone know how i can open the txt file delimited?

Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String

' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of the file
'MsgBox "You selected " & FileName

' Display full path and name of the file
MsgBox "You selected " & FileName
Workbooks.Open FileName
End Sub

Dim FPath As Variant, FInfo As String
FInfo = "Excel Workbook (*.xlsx), *.xlsx"
FPath = Application.GetSaveAsFilename("Filename", FInfo)
ActiveWorkbook.SaveAs Filename:=FPath, FileFormat:=xlExcel12

The code is throwing up "Run-Time error '1004': This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type."

I've tried msgbox on the FPath variable and it looks fine: "C:\...\Filename.xlsx"

Please help!

The top part of the Sub below is written to select multiple files and to create multiple sheets to accommodate the data in these files. One sheet per file. This part is working.

The last loop in the Sub imports the data. Currently the this loop can only import a file named: M:\2.xml.
How do I need to change the code so that it imports all the files I selected?


Sub ImportXMLFiles()

'Define variables
    Dim Filt As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant
    Dim i As Integer
    Dim j As Integer

'Select files to Import

        Filt = "XML Files (*.xml) ,*.xml," & _
                "All Files (*. *), *. *"
        FilterIndex = 5
        Title = "Select a File to Import"
        FileName = Application.GetOpenFilename _
            (FileFilter:=Filt, _
            FilterIndex:=FilterIndex, _
            Title:=Title, _

'Create sheets, one for each file to import
    For j = LBound(FileName) To UBound(FileName) - 1
        Sheets.Add After:=Sheets(j)
        Sheets(j + 1).Select
        Sheets(j + 1).Name = j + 1
    Next j

'Import files

   For i = LBound(FileName) To UBound(FileName)
       ActiveWorkbook.XmlImport URL:="M:\2.xml", ImportMap:=Nothing, Overwrite:= _
            True, Destination:=Range("$A$1")
   Next i

End Sub

Hi all

I am using the following code to allow the user to select a file they wish to process:

strFileLocation = Application.GetOpenFilename(FileFilter, FilterIndex, Title)

that stores the path of the file into the variable.
what I would like is to store the actual filename, so that when it is open, I can then use the filename to switch between workbooks.

Can anyone tell me how I can store just the name?
(even if it is stripping the string after the last '\')

thanks in advance,

Hello Experts,

I have below code in one of my macro projects:


RunBookCrtF = Application.GetOpenFilename("Runbook file in YYYY-MM-DD format (*.xlsx),*.xlsx", 1, "Select latest date Runbook Critical Fields Report file")

This will prompt the user to open one workbook which is required for executing a macro. When this file open dialog box appears on screen it is displaying 4-5 different filenames in default folder under which this dialogbox is displayed.

My requirement is it should display only filename starting with 20*.xlsx because the file I want the user should open is in YYYY-MM-DD.xlsx format and it can be any date from this week ex. 2010-04-27.xlsx.

This way I can prevent user from selecting a wrong file to be opened.

Is that possible to apply this kind of filter?


I have the code below that opens files in a particular folder... I need it to open them in the order that they are in the file (alphabetical by filename). Is this possible?

Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\SAP Imports\Sales Orders\"

If Right(folderPath, 1) "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.xls")
Do While filename ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)

Call Mymacro

filename = Dir
Application.ScreenUpdating = True
End Sub


I have several excel files stored on a 16gb usb stick. I use these files from the same computer daily, I have tried to open several files today in Excel 2007. It gives me the following error:

"Excel cannot open the file '(filename)'.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."
I tried ctrl shift I and got the code 101786, if i click yes the page then opens text import wizard which says my my data is delimited.

I have tried:
Opening in 2003 - the same prob,
I have changed the file names to .xls - still doesnt work

I've tried the "Open and Repair" option of Excel, but to no avail.
I tried copying it with no luck.
I tried 3 different computers...nothing!
I have made duplicates emailed etc but nothing works.


Can someone please help me out, I cant understand how this has all of a sudden happened - I use these files on a regular basis. I have so much work stored that i cant access! these are really important work files,

I am no expert so a reply in simple terms would be really appreciated

Dear All,

I am trying to use the following code to open first an Excel file and then a CSV file. the Excel filter is fine. However I cannot get the filter to show only CSV Files (see the line in bold which ought to restrict the view to just CSV files - bu gives me Excel files). Thanks



Dim file_name As String

Dim FilterIndex As Integer
Dim filter As String
Dim fd1 As FileDialog
Dim fd2 As FileDialog

'open the credit card file

Set fd1 = Application.FileDialog(msoFileDialogFilePicker)

With fd1
    .AllowMultiSelect = False
   ' .InitialFileName = local_path
    .Title = "Please select Bank file"
    .Filters.Add "Excel files", "*.xls; *.xlsx", 1
    If .Show = -1 Then file_name = .SelectedItems(1)
End With

If Len(file_name) > 0 Then
    'open the workbook
    Workbooks.Open file_name
    Set Credit_card_workbook = ActiveWorkbook
End If

'open the Sage CSV file

Set fd2 = Application.FileDialog(msoFileDialogFilePicker)

With fd2
    .AllowMultiSelect = False
   ' .InitialFileName = local_path
    .Title = "Please select Sage file"
    .Filters.Add "text files| ", "*.csv"
    If .Show = -1 Then file_name = .SelectedItems(1)
End With

If Len(file_name) > 0 Then
    'open the workbook
    Workbooks.Open file_name
    Set Sage_Input_workbook = ActiveWorkbook
End If



I'm trying to import data from delimited text files without Excel saving the query or data connection. I used the Macro Recorder during the manual import, and unchecked the default "Save query definition" and selected "overwrite..." from the Import Data --Properties--External Data Range Properties dialog box:

Sub ImportDataWithUI() 
     ' ##############   BEGIN DATA IMPORT ##################
     ' Import overwrites cell range without inserting new columns
     'Get the file name with a UI from
     'Walkenbach's Sub GetImportFileName() page 410
    Dim Filt As String 
    Dim FilterIndex As Integer 
    Dim Title As String 
    Dim FileName As Variant 
     '   Set up list of file filters
    Filt = "Text Files (*.txt),*.txt,(*.csv),*.csv,(*.dat),*.dat," & _ 
    "All Files (*.*),*.*" 
     '   Display *.* by default
    FilterIndex = 1 
     '   Set the dialog box caption
    Title = "Select a File to Import" 
     '   Get the file name
    FileName = Application.GetOpenFilename _ 
    (FileFilter:=Filt, _ 
    FilterIndex:=FilterIndex, _ 
     '   Exit if dialog box canceled
    If FileName = False Then 
        MsgBox "No file was selected." 
        Exit Sub 
    End If 
     '   Display full path and name of the file
     '    MsgBox "You selected " & FileName
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;" & FileName _ 
        , Destination:=Range("$A$1")) 
        .Name = FileName 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlOverwriteCells 
        .SavePassword = False 
        .SaveData = False 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 437 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = False 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Unfortunately, the code recorded fails to remove the query definition. So I searched around an found two subs that should do the trick, but don't:


 ' From:
Dim Sh As Worksheet, xNazwa As Object 
Dim xConect As Object 
For Each xConect In ActiveWorkbook.Connections 
    If UCase(xConect.Name) Like "*" Then xConect.delete 
Next xConect 
For Each Sh In ActiveWorkbook.Worksheets 
    For Each xNazwa In Sh.Names 
    Next xNazwa 
Next Sh 
Dim ws As Worksheet 
Dim qt As QueryTable 
For Each ws In ThisWorkbook.Worksheets 
    For Each qt In ws.QueryTables 
    Next qt 
Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

You see, I need to make a table from this data (bogus example file is attached) and if there is a data connection or a query, Excel throws a runtime error 1004 (table cannot intersect query). Once I get the data in, I run the following code to get the size of the data range (where the header row is, where the data rows start and end, and the last column). This code works until I turn it into a table, where things have ground to a halt. Here's the last part of the code:

Dim DataCellStart As Range 
Dim DataHeaderRow As Integer 
Dim DataRowStart As Integer 'for starting data calculations
Dim DataRowEnd As Long 
Dim DataColEnd As Integer 'this is the last column of imported data
Application.GoTo Reference:="R1C1" 
DataHeaderRow = Selection.Row 
DataRowEnd = Selection.Row 
DataColEnd = Selection.Column 
DataRowStart = DataHeaderRow + 1 
Range("E1") = "Data Header Row" 
Range("E2") = DataHeaderRow 
Range("E3") = "Last Data Row" 
Range("E4") = DataRowEnd 
Range("E5") = "Last Data Column" 
Range("E6") = DataColEnd 
 'Automatic sizing of column widths, selecting like ctrl+shift+*
 'Auto-size columns
 'create a table from the imported data and give it a name
ActiveSheet.ListObjects.Add(xlSrcRange, , xlYes).Name = "ImportedData" 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

There was only one post in ozgrid that I found with the same problem but there was no solution:

I'll be grateful for any suggestions. I hope the explanation and breakdown is clear.

Hi Folks,

I am dynamically generating and naming an xl file based on some conditions. The use the following code-

Dim newFile As Long
newFile = FreeFile()
If FileName "" Then
Open "C:\imacro_files\" & FileName & ".xls" For Binary Access Write Lock Write As newFile
Close newFile

Application.Workbooks.Open FileName:="C:\vba_files\" & FileName & ".xls"

The problem here is if I try to save and open it as xlsx it gives an error like- Excel cannot open the file because the file format or file extension is not valid. Make sure file extension matches the format of the file.

Next, if I go ahead with using .xls and then format a column to have zeros appended to it the formatting just doesnt work. I get an error like- The file is Text (tab delimited file) and so it cant support the formatting. Now I not sure how do I handle this as I absolutely need to format the column. I tried using all kinds of functions and also storing it as a string but nothing works. All formatting is just removed and at the same time it doesn't even let me open it if in .xlsx format (as stated initially).

So, basically it would help if I could get either problem solved-
1. Be able to save and open the file as .xlsx
2. Be able to format the text tab delimited file or prevent the file from getting saved as text tab delimited
3. Any other workaround?

PS: The funny thing is though I get an error about the file being text tab delimited it still shows the format as .xls (Microsoft Office 97/2003)
Also, it might help to know that in the orgin file I am importing data from an xml file. From this origin file data is then transferred to this new dynamic file.
xml -> xlsm --> xls
Any inputs will be much appreciated.


I have 214 xlsx files i need to convert to csv format for mapping in another program
ive been searching forums etc for a day and more and have got some code that looks as if it should work, but i'm pretty new to this so it could be totally wrong.
ultimately i want to code to get all of the files in the xls folder and make csv versions with the same filename.

anyway, its failing on Workbooks.Open Filename:=MyFileName
here is the code


Sub LoopFiles()
    Dim MyFileName, MyPath As String
    Dim MyBook As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    MyPath = "D:\ArcGis_Projects\Elephants\csv\xls\"
    MyFileName = Dir(MyPath & "*.xlsx")
    Do Until MyFileName = ""
        Workbooks.Open Filename:=MyFileName
    ChDir "D:\ArcGis_Projects\Elephants\csv_dm"
    ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

thanks in advance for for any help! Im sure this is a very simple fix



I have this code that is supposed to open excel file, manipulate them, then save the end result as a csv file. It doesn't...


   Const pathPattern As String = "C:\*.xls*"
   fileName = Dir(pathPattern)
   Do While fileName  ""
      Set wb = Workbooks.Open(fileName)
        With wb.Worksheets(1)
'Do something
        End With
      wb.SaveAs fileName:=wb.Path & "\" & fileName, FileFormat:=xlCSV, CreateBackup:=False
     wb.Close True
    fileName = Dir

It always tries to replace the original excel sheet (exact name as original file). When I changed:


      wb.SaveAs fileName:=wb.Path & "\" & fileName, FileFormat:=xlCSV, CreateBackup:=False



      wb.SaveAs fileName:=wb.Path & "\" & fileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False

then it kept the original file name (including the .xlsx) and added a .csv to the end.

Where am I going wrong???


I am having a problem with Application.GetOpenFilename.

I am opening up a dialog box that allows a user to choose one or more files.

Briefly, the problem is this. Everything works well in the code below unless the user clicks cancel on the dialog box. Then I get a run time error '13': Type mismatch. On the line with Application.GetOpenFilename. My code is below.

Any thoughts would be appreciated, this is driving me crazy!!


Private Sub CommandButton4_Click()
    'variant allows for the selection of multiple files.
    Dim FileName() As Variant
    Dim tmpFiles As String
    Dim i As Long
    Dim ProjOutFolder
    Dim ProjDir As String
    Dim ResultsFile As String
    Dim j As Integer
    Dim ManageArray() As String
    Dim LastCol As Integer
    Dim tmpFileName As String
    Dim PathFileName() As String
    Dim a As Integer
    Dim StrCompNum As Integer
    With Sheets("Manage")
        'Get and format the Existing project or file name
      '  Set ProjOutFolder = .Cells.Find(What:="Output Folder", After:=.Cells(1, 1), LookIn:=xlValues, _
      '          LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Offset(0, 1)
        If Dir(ProjOutFolder, vbDirectory) = vbNullString Then
            MsgBox "The Output Folder path on the Manage tab is not found. If your path is a UNC network drive, please use a mapped drive instead."
            Exit Sub
            'change this to the first character of the project out folder:
            If Left(Trim(ProjOutFolder), 1) Like "[A-Za-z]" Then
                ChDrive Left(Trim(ProjOutFolder), 1)
            End If
            ChDir (ProjOutFolder)
 	    'Type mismatch error occurring here - only when cancel is clicked.
            FileName = Application.GetOpenFilename("NHK Files, *.nhk,All Files, *.*", , , , True)
            On Error GoTo CancelClicked
            With Application
                'Reset Start Drive/Path
                If (Left(.DefaultFilePath, 1)) Like "[A-Za-z]" Then
                    ChDrive (Left(.DefaultFilePath, 1))
                End If
                ChDir (.DefaultFilePath)
             End With
        	If IsArray(FileName) Then
                'split out FileName: remove the output path, and split out
                'the base filename and the batch run label
                'Loop through array and display each file name selected.
                ReDim PathFileName(1 To UBound(FileName), 1 To 4)
                For i = LBound(FileName) To UBound(FileName)
                    'pull out portion after last occurring slash
                    tmpFileName = Right(FileName(i), Len(FileName(i)) - InStrRev(FileName(i), "\"))
                    PathFileName(i, 1) = FileName(i)
                    PathFileName(i, 2) = Trim(Left(tmpFileName, InStr(tmpFileName, " - Baseline")))
                    'Pull out path name
                    PathFileName(i, 3) = Left(FileName(i), (InStrRev(FileName(i), "\") - 1))
                    PathFileName(i, 4) = Replace(tmpFileName, ".nhk", "")
                Next i
        End If
End With
    If Not IsArray(FileName) Then
        Exit Sub
    End If
End Sub


I have some templates saved as .xls files on a network share, and this folder is linked through the User Templates setting in Office 2000.

When I do File - New in Excel, and select a template to open (eg X1000.xls), it opens then closes straight away. If I rename the file to X-1000.xls, it opens and stays open... I don't want to change the filenames as they are corporate forms that are linked to our procedures.

Does anybody know why Excel is closing the file based on the filename? I have tried saving the file as an xlt, but the same applies.


Hello everyone!

I have a problem, and while I do have backup files (a week old), I cannot think of any reasonable explanation for what is happening.

Last night a co-worker was working on a Shared File, was doing some text inputs (adding dates & notes) and some format changes (highlighting certain cells). They saved and closed out the file (in .xlsx format as it has always been) using Excel version (12.0.6504.5001) SP1 MSO (12.0.6320.5000).

This morning, when the tracker was being opened, an error displayed as follows:

"Excel found unreadable content in 'filename.xlsx'. Do you want to recover teh contents of this workbook? If you trust the source of this workbook, click Yes. (Yes) (No)"

I hit Ctrl-Shift-i to get the code for the error (101648)

Clicking no the first time and making a copy of the file, then click Yes. Yet another error is displayed as follows:

"Excel cannot open the file 'filename.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. (OK)"

I hit Ctrl-Shift-i to get the code for that error (101590)

I then tried to open a new workbook and link it to certain cells, but it morphs my formula and returns a #REF error

The original formula I type in is:


='[S:\File Location\filename.xlsx]Sheet1'!A1

The formula that I see after I hit enter is:


='[S:\File Location\[filename.xlsx]Sheet1]filename.xlsx]'!A1

Which is just odd in and of itself, so then I started browsing google to see if anyone else had encountered such an issue, and while I could find responses on the errors alone, I couldn't find anything that matched what was happening to me.

I also tried to rename the file to .xls and .xlsb and tried to open the file. As well as using Excel 2003 with the converter to open the file.

So far everything I have tried has failed to work.

Has anyone seen anything like this? Have any thoughts on solutions?

Your help is greatly appreciated!

Thank You,

Hi everyone,
I have the following code to browse a file in disck. But I get an error message over "If desPathName = False Then". How come?

Sub browseFile()
    desPathName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Please select a file")
    If desPathName = False Then
        MsgBox "Stopping because you did not select a file. Reselect a destination file through the menu"
        Exit Sub
        Workbooks.Open Filename:=desPathName
        Set des = Workbooks.Open(desPathName, True, False)
        Set desSheet = des.Worksheets(desWorksheetName)
    End If
End Sub


I am having trouble ending a macro when the user clicks cancel on the open file dialog box that opens when they run the macro. GetOpenFilename returns false when you click cancel, but I can't get any further than that.

Thank you!


Sub LoadLV()

Dim wb As Workbook
Dim pickfile As Variant
    'Show the open dialog and pass the selected file name to "pickfile"
    pickfile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
        Title:="Choose File", MultiSelect:=False)
    'If they have cancelled
    If pickfile = "False" Then End Sub

    Set wb = Workbooks.Open(pickfile, True, False) ' open the source workbook with write permissions
    ActiveCell.Offset(0, 19).Range("A1").Select
    ActiveSheet.Paste Link:=True
end sub

Hopefully someone out there can help with a macro problem I am having in '07.

The macro opens a workbook and the workbooks that it is linked to so the form can udate. After the update a copy is saved with the new information which can then be viewed by all users and stored for archive reasons. A new file is created each time that the macro is run so this is not overwritting the same file over and over again.

The problem is that the saved workbook still contains all of the links that were in the original file. Does anyone know a way that I can save the file as values only without the links?

Application.Visible = False
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\Communication Sheet.xlsx", _
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs unshipped.xls"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWorkbook.SaveCopyAs "\\Rpf-baltimore\shared\PRODUCTION\Production Reporting\communication sheet\CS_" & Format(Date, "mmddyy") & "_" & Format(Time, "hhmm") & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False

I am having some issues with a macro that I wrote, when trying to save a file on some users computers.

The macro has to run in Excel 2007 (or newer) and check for this condition at the outset.

The troublesome part of my marco occurs when it copies a sheet from an excel 2003 file (.xls) and tries to save as a 2007 file (.xlsx). One particular user keeps getting the error:
"Run-time error '1004'
Microsoft Office Excel cannot access the file ' c:\c4c320000 '"
The c:\c4c320000 always seems to change and is never the same as the filename variable.

I'm wondering if this truly a macro issue, or some setting in Excel/Windows?

The code is:
'Define temp file name
benchmark_name_temp = ws_name & "_" & Replace(fire_file_name, ".xls", "") & ".xlsx"
benchmark_name_and_path_temp = "c:\" & benchmark_name_temp

'Save as Excel 2007 file, close and reopen so 1 million rows are available for data
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=benchmark_name_and_path_temp, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close savechanges:=False
Workbooks.Open filename:=benchmark_name_and_path_temp


Hello I have made a program in Excel which will print out an Acrobat PDF file after the print button is pressed. The problem is that I cannot make the PDF file not to open and still print. Here is my code

Dim FileName As String '
FileName = "I:\Test\test.pdf"
Set myShell = CreateObject("WScript.Shell")
myShell.Run ("Acrobat.exe /t" & FileName)

I looked at the command line in window documentation from Adobe and have used /h, even with the /h itself, the file still opens up.

Thank you,


I want to be able to save a report that is created by a macro with the SaveAs dialog box showing the default Path, the default Filename and a default File Extension (.xlsx).

I used to be able to write something like


ActiveWorkbook.SaveAs (SavePathAndFileName) 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

but it doesn't seem to work with Excel 2010 producing this error message
Run-time error '1024':
This extension can not be used with the selected file type. Chnage the file extension in teh Fike name text box or sekect a differnt file type by changi8ng te Save as type.

Here is my full code.


Public Sub SaveFileAs() 
    Dim TodaysDate As String 
    Dim RawDataFileName As Variant 
    Dim SaveFileName As Variant 
    Dim SavePath As Variant 
    Dim SavePathAndFileName As Variant 
    TodaysDate = GetTodaysDate 
    SaveFileName = "Barclays Data " & TodaysDate 
    SavePath = "C:\Users\Robin\Desktop\Documents\Macros\Save As" 
    SavePathAndFileName = SavePath & "\" & SaveFileName 
    SaveWorkbookAs (SavePathAndFileName) 
End Sub 'SaveFileAs
Private Function GetTodaysDate() 
    GetTodaysDate = Format(Now(), "dd-mmm-yy") 
End Function 'GetTodaysDate
Private Sub SaveWorkbookAs(SaveFileName As String) 
    Dim FileExtension As String 
    Dim SaveFile As String 
    FileExtension = "Excel Files 2010 (*.xlsx), *.xlsx," & _ 
    "All files (*.*), *.*" 
    SaveFile = Application.GetSaveAsFilename( _ 
    InitialFileName:=SaveFileName, _ 
    MsgBox "Save File is called " & SaveFile 
    ActiveWorkbook.SaveAs (SaveFile) 
End Sub 'SaveWorkbookAs

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I have this macro to go to a specific folder and open up all of the files in the folder and merge them into a worksheet.
I want to change it so the user can select the files to be merged.

any suggestions?

Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer
RowofCopySheet = 2 ' Row to start on in the sheets you are copying from
ThisWB = ActiveWorkbook.Name

path = ("F:\Action O-I\Numeric Data Upload\Numeric Response Per Dept\Test")
Application.EnableEvents = False
Application.ScreenUpdating = False
Set shtDest = ActiveWorkbook.Sheets("Upload File")
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
Sheets("Upload File").Select
Dest.PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False 'Clear Clipboard
Wkb.Close False
End If

Filename = Dir()


Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox "Done!"
End Sub

I'm running Excel 2007 on a WinXP (SP2) machine (dual core, 4GB RAM, 8GB free HDD space).

I keep getting a strange error message frequently when I try to save large files (> 2MB) with lots of sheets. Excel seems to save okay (the save progress bar at the bottom finishes), but then a dialog box pops up saying:

Your change could not be saved to filename.xls because of a sharing violation. Trying saving to a different file.

Once I hit Ok on that dialog box, it immediately gives another dialog:

The file you are trying to open, '56C2F10', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? Y/N

I hit Yes, and it then opens another version of the same file and gives me the Save As... dialog box. Once I enter a different name, it saves that file and closes it, leaving me with the original file that I was trying to save (but could not). I then close that original file and open the new/renamed file and it contains all my changes.

As I mentioned, this happens usually with large files (>2MB), usually ones which have many sheets (>10). It has happened with .xls, .xlsx, and .xlsm formats. I am saving only to my local hard drive, and the file is not shared with/on any other user/server/machine.

It is very annoying, since I have to interrupt my work to close the "corrupted" file and open the newly renamed one, not to mention that I end up with multiple copies of the files.

Any insight as to what's causing this problem, and how to fix it?

Using Excel 2007:
I am trying to use dir() in a While...wend to get a specific list of files to process, but I want the list to only include those with a .xls extension, not those ending in .xlsx. It seems that the file filter ".xls" doesn't exclude those files with a ".xlsx" extension.
Sample code demonstrating the problem (will show the problem if there is at least one cycle that meets a*.xls and one that meets a*.xlsx).
Is there a workaround to this at the dir() level? Or do I need to go ahead and open the file and test its contents?

Sub TestDir()
    On Error GoTo Nofile   'trap cancel, X-out of the dialog, etc.
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .InitialFileName = "a*.xls"
        GetDir = .SelectedItems(1)
    End With
    On Error GoTo 0       'reset error handler
    If GetDir  "False" Then
        Path = CurDir & "\"
        MsgBox "Directory not selected"
        Exit Sub
    End If
    df = Dir(Path & "a*.xls") 'should get the list of a*.xls files in the directory, but gets a*.xlsx too
    While df  ""
        MsgBox (df)
    df = Dir()
    GoTo OK     'skip the error message
    MsgBox ("No file selected.  Macro will exit.")
    Exit Sub

End Sub

Thanks for any help,

I'm using Excel 2003.

I'm looping through files, opening them, and writing info into another Excel spreadsheet. I need to check for corrupt excel files. I'm getting close, but I'm getting the error message "this file is not in a recognizable format" and the user would have to remember to select "cancel" (and not "OK"). I'd like to suppress this error message.

My code:

In my sub:

Global FileIsCorrupt as boolean
Global NewFileToCheck as string
Global FileName as string
Global Path as string

(Filename and path are set at this point...)

OpenFileAndCheck 'call function

If FileIsCorrupt = True then 'if true, file is corrupt or unrecognizable
msgbox ("msg to user that it is corrupt and file is being skipped')
GoTo FoundCorruptFile ' skips over writing info from file
End if

Function OpenFileAndCheck() as Boolean

On Error GoTo ErrHandler
FileIsCorrupt = False 'set to false--it will be reset to true if file is corrupt

Set NewFileToCheck = Workbooks.Open(Filename:=Path & FileName)

Exit Function

If Err 0 then
FileIsCorrupt = True
End if

End Function

Your help would be so appreciated!