Need an IF statement to open an .xls or .xlsx workbook

MartyChase · Jan 10, 2012

Hello Everyone,

I need some help with a macro that I am working on. I use the Macro within one workbook to open up to 33 other workbooks, copy information and paste it into the initial workbook. The problem has come up that within the 33 workbooks, some are saved as .xls and others are saved as .xlsx. Instead of going into each .xlsx workbook and saving them back to .xls I would like to adapt the macro to be able to read both formats.

Currently the macro reads as this:
 Workbooks.Open Filename:= _
"\\File name here ".sws.xls"

I assume I will need an IF statement but have been unsuccessful in finding the correct one.

Thanks in advance.

Marty

Joe4 · Jan 10, 2012

Welcome to the Board!

I use the Macro within one workbook to open up to 33 other workbooks

How are you opening up each one?
By name directly (if so, just include the correct extension in each name reference) or by a loop?

It might help to actually post the details of your code (even if it isn't working exactly right) so we can see the methodology you are trying to use.

MartyChase · Jan 10, 2012

Thanks!

I am opening the individual files up with a Loop and have set a variable to account for the different names of the files. Each file is collecting data for a different location and each location is part of the file name. Also within the file name there are variables set for month and year.

If not an IF statement then maybe an error handler?

Thanks again,

Marty

Joe4 · Jan 10, 2012

I am opening the individual files up with a Loop and have set a variable to account for the different names of the files.

Please post your code so we can see how this loop code is written.

MartyChase · Jan 10, 2012

currentmonth = ActiveWorkbook.Name
currentmonth = Mid(currentmonth, 12, 3)
montharray = [{"JUL", "AUG", "SEP", "OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "APR", "MAY", "JUN"}]
monthnumber = Application.Match(currentmonth, montharray, 0)
If monthnumber < 10 Then
monthnumber = "0" & monthnumber
End If
X = 0
Do Until X = 33
X = X + 1
Rnst = Choose(X, "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA")
Workbooks.Open Filename:= _
"\\Hcs505Tfile001\unit shares\Siscal\SWS\" & fy & "\Received\" & monthnumber & "-" & currentmonth & "\" & inst & ".sws.xls"

Is this what you are looking for?

MartyChase · Jan 10, 2012

I need to bypass the error message that pops up telling me that the .xls file does not exist when it is saved in the .xlsx format.

Is this possible?

Joe4 · Jan 10, 2012

Yes, that is what I was looking for. Try replacing your Open line of code (last line in your code) with this block:

Code:

myFileName = "[URL="file://hcs505tfile001/unit"]\\Hcs505Tfile001\unit[/URL] shares\Siscal\SWS\" & fy & "\Received\" & monthnumber & "-" & currentmonth & "\" & inst & ".sws.xls"
If Dir(myFileName) <> "" Then
    Workbooks.Open Filename:=myFileName
Else
    If Dir(myFileName & "x") <> "" Then
        Workbooks.Open Filename:=myFileName & "x"
    Else
        MsgBox "No file found"
    End If
End If

MartyChase · Jan 10, 2012

Thanks Ill see if I can get this to work and let you know!

MartyChase · Jan 10, 2012

On Error Resume Next
Workbooks.Open Filename:= _
"\\Hcs501jfile001\unit shares\Fiscal\Suicide Watch System\" & fy & "\Received\" & monthnumber & "-" & currentmonth & "\" & inst & ".sws.xls"
If Dir(myFileName) <> "" Then
Workbooks.Open Filename:=myFileName
Else
If Dir(myFileName & "x") <> "" Then
Workbooks.Open Filename:=myFileName & "x"
Else
MsgBox "No file found"
End If
End If

I have the "On Error Resume Next" line of code which will bypass the RuntimeError 1004 but I still am getting a window popping up stating that "\\Hcs501jfile001\unit shares\Fiscal\Suicide Watch System\" & fy & "\Received\" & monthnumber & "-" & currentmonth & "\" & inst & ".sws.xls" cannot be found. Check spelling or try a different path." I then have to click the "OK" button to proceed with the macro. Ultimately what I would like to do is run the code without any windows popping up and having the code check for .xls or .xlsx whichever one happens to exist for the specific location.

Is there anyway to bypass this second popup window?

Also, now I am getting the "No Msg found" box popping up and I am not sure why I need that.

-Marty

Joe4 · Jan 10, 2012

Yuo didn't apply my code correctly. You still have your original Open statement in there. Get rid of that. You shouldn't need any error handling, the code check for the existence of a file by that name before opening it.

Code:

myFilename= _
"\\Hcs501jfile001\unit shares\Fiscal\Suicide Watch System\" & fy & "\Received\" & monthnumber & "-" & currentmonth & "\" & inst & ".sws.xls"
If Dir(myFileName) <> "" Then
    Workbooks.Open Filename:=myFileName
Else
    If Dir(myFileName & "x") <> "" Then
        Workbooks.Open Filename:=myFileName & "x"
    Else
        MsgBox "No file found"
    End If
End If

Need an IF statement to open an .xls or .xlsx workbook

MartyChase

New Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

MartyChase

New Member

Joe4

MrExcel MVP, Junior Admin

MartyChase

New Member

MartyChase

New Member

Joe4

MrExcel MVP, Junior Admin

MartyChase

New Member

MartyChase

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

Need an IF statement to open an .xls or .xlsx workbook

New Member

Excel Facts

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

New Member

New Member

MrExcel MVP, Junior Admin

New Member

New Member

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock