Non-Robotics: VBA for Excel?

Technical Programming
1

I have vba code that works with Excel 2007 just fine. A co-worker now has 2013 installed and is getting errors. Is there a reason the following line wouldn’t work with 2013?

Workbooks(2).ActiveSheet.Columns(“V”).Select

Thanks,
indieFan

2

Are there actually two workbooks opened on the co-worker’s system when that line is run? What is the error?

3

There are two workbooks opened. The other functions that call out those workbooks seem to do ok for now.

The error code is 1004 “Select method of range class failed”, I think. I had the co-worker run it on his system.

4

remove workbook reference from the sub

ActiveSheet.Columns(“V”).Select

5

That seemed to fix the issue, although I’m not sure why. Now I have a problem when saving the file that I can’t solve. The code that works just fine for Excel 2007, but not 2013.

My code is as follows:

Private Sub SaveFormattedFile()
'This function saves the workbook in the same location with the .xlsx extension.

Dim NameOfWorkbook As String, FileNameToSave As String 'Declare variable

'Get the filename without the extension.
NameOfWorkbook = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

FileNameToSave = NameOfWorkbook & ".xlsx" 'Add the new file extension to save.

'Save the workbook as a .xlsx file.
ActiveWorkbook.SaveAs FileNameToSave, FileFormat:=51

End Sub

6

Looks like you are saving your spreadsheet as a non macro enable spreadsheet but you have a macro in it. Try format 52

http://blogs.office.com/2009/07/07/use-the-vba-saveas-method-in-excel-2007/

7

  1. I already tried using 52, but still got errors.

  2. The macro opens a blank worksheet. The files that it’s handling are all external. It opens a .csv file first which is what is manipulated with data from the other two files. It then tries to save the .csv as a .xlsx. Again, it works with 2007 without issue, but 2013 causes errors.

8

If a workbook is minimized in Excel, is it no longer considered active?

9

I hate to ask this… but are you sure the macro’s workbook isn’t trying to save itself as a .xlsx? Because that would cause a lot of problems. I can’t tell what the active workbook happens to be–there’s no call within the subroutine posted to select and activate a particular workbook (though, granted, that could easily be being handled elsewhere).

Try specifically setting which workbook needs to be saved (just make sure it’s always in the same spot in the list of workbooks). I had some really screwy issues in one of my macros a while back and ended up telling the macro very specifically that it was to look in THIS sheet or THAT sheet when it was looking for whatever I was having it look for, rather than selecting the sheet and looking in it. Problem went away as soon as I did that.

Short version: Try telling Excel specifically which workbook is the active workbook that needs to be saved.

10

I had it written at one point as “workbooks(2).saveas filenametosave fileformat:=51”. That still wasn’t working.

What did resolve the issue was no longer minimizing the workbook. It now works on 2013. My guess is that because the macro workbook is open and maximized at the time of running, minimizing the first file renders it inactive. Still not sure why the “Workbooks(2).saveas” didn’t work.

Thanks to everyone for the suggestions and ideas.