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

MartyChase · Jan 11, 2012

Almost there, I think...

Now I have the BOLD part of my code below which is telling me that the file is "out of range". I assume because it is still referring to the .xls file when the file open is actually .xlsx.

Windows("SWS.Rollup." & currentmonth & ".xls").Activate
ActiveCell.Value = inst
ActiveCell.Offset(0, 1).Value = CDCR
ActiveCell.Offset(0, 2).Value = LNAME
ActiveCell.Offset(0, 3).Value = FName
ActiveCell.Offset(0, 4).Value = DateAd
ActiveCell.Offset(0, 5).Value = timeadd
ActiveCell.Offset(0, 6).Value = dateleft
ActiveCell.Offset(0, 7).Value = timeleft
ActiveCell.Offset(1, 0).Select
Windows(inst & ".sws.xls").Activate
If ActiveCell.Offset(Y, 3) = "" And ActiveCell.Offset(Y, 4) = "" And ActiveCell.Offset(Y, 5) = "" And ActiveCell.Offset(Y, 6) = "" Then
ActiveCell.Offset(7, 0).Select

Joe4 · Jan 11, 2012

OK, let's modify my prior code slightly so we store the extension in a variable (note the minor changes to a number or lines):

Code:

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

Now, we should be able to replace this:

Code:

Windows(inst & ".sws.xls").Activate

with this:

Code:

Windows(inst & myFileExt).Activate

Justin Hardaway · Jan 11, 2012

I had a very similar issue but for much fewer workbooks, I ended up cutting the string into various parts and placing them into cells with a list of the variables that were dependant on the file I was trying to pull and setting the loop to write those parts into their appropriate cells the refrence a diffrent cell that combined all the variables into one string, ill try to post an example a little later

MartyChase · Jan 11, 2012

Joe,

It now seems with that latest change I will loose the ability to open a file with the extension .xlsx? After it doesn't find the .xls file the message box pops up and says "No File Found"

Does that make sense?

MartyChase · Jan 11, 2012

Justin,

I don't think that will work for this application because there are times when the file extension is .xls and others when it is .xlsx and there is no telling what the file extension is going to be on the different files. I dont think I can create one string when the file extension could be different on each individual file.

Am I reading your post correctly?

Marty

Justin Hardaway · Jan 11, 2012

MartyChase said:
Am I reading your post correctly?

Marty

It would seem so if you dont know which file will have which, I could find and clearly define the variable for each of my agents so it really was just a matter of listing them and telling the macro where to go to refrence the next person. But if it can change with no notice, then it would not work for you.

Joe4 · Jan 11, 2012

Marty,

All of what you said is already taken into account. The logic behind it all hasn't changed. Notice the value in that variable changes depending on whether or not it is an "xls" or "xlsx" file. Did you copy my code as is, or type it manually, or try to modify it?

If you cannot get it to work, please post your code, as you currently have it.

MartyChase · Jan 11, 2012

Joe,

Realized that I missed a line of your updated code when I typed it in, IT WORKS!!!

You are awesome, thanks for the help!!!!

Marty

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

MartyChase

New Member

MartyChase

New Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Justin Hardaway

New Member

MartyChase

New Member

MartyChase

New Member

Justin Hardaway

New Member

Joe4

MrExcel MVP, Junior Admin

MartyChase

New Member

Similar threads

Forum statistics

Share this page

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

New Member

New Member

Excel Facts

MrExcel MVP, Junior Admin

New Member

New Member

New Member

New Member

MrExcel MVP, Junior Admin

New Member

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