Go to Post Yes, an expanded FIRST will look far different than it does today. - GaryVoshol [more]
Home
Go Back   Chief Delphi > Technical > Programming
CD-Media   CD-Spy  
portal register members calendar search Today's Posts Mark Forums Read FAQ rules

 
Closed Thread
Thread Tools Rate Thread Display Modes
  #1   Spotlight this post!  
Unread 04-11-2014, 13:15
indieFan indieFan is offline
RoboDox and LVHS - Missing you!
FRC #5941
Team Role: Engineer
 
Join Date: Jan 2002
Rookie Year: 2001
Location: Seattle (was SoCal, then SA,TX))
Posts: 382
indieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond repute
Non-Robotics: VBA for Excel?

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   Spotlight this post!  
Unread 04-11-2014, 13:39
Alan Anderson's Avatar
Alan Anderson Alan Anderson is offline
Software Architect
FRC #0045 (TechnoKats)
Team Role: Mentor
 
Join Date: Feb 2004
Rookie Year: 2004
Location: Kokomo, Indiana
Posts: 9,113
Alan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond reputeAlan Anderson has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

Quote:
Originally Posted by indieFan View Post
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
Are there actually two workbooks opened on the co-worker's system when that line is run? What is the error?
  #3   Spotlight this post!  
Unread 04-11-2014, 13:46
indieFan indieFan is offline
RoboDox and LVHS - Missing you!
FRC #5941
Team Role: Engineer
 
Join Date: Jan 2002
Rookie Year: 2001
Location: Seattle (was SoCal, then SA,TX))
Posts: 382
indieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

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.

Last edited by indieFan : 04-11-2014 at 13:48.
  #4   Spotlight this post!  
Unread 04-11-2014, 14:54
roystur44's Avatar
roystur44 roystur44 is offline
Mentor/Sponsor
AKA: Roy Dumlao
FRC #4543 (Apollo Robotics)
Team Role: Mentor
 
Join Date: Mar 2010
Rookie Year: 2006
Location: San Jose,California
Posts: 363
roystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond repute
Send a message via Yahoo to roystur44
Re: Non-Robotics: VBA for Excel?

remove workbook reference from the sub


ActiveSheet.Columns("V").Select
__________________
Roy Dumlao

Mentor/Sponsor
2006-2012 971
2013-2017 4543
  #5   Spotlight this post!  
Unread 06-11-2014, 15:39
indieFan indieFan is offline
RoboDox and LVHS - Missing you!
FRC #5941
Team Role: Engineer
 
Join Date: Jan 2002
Rookie Year: 2001
Location: Seattle (was SoCal, then SA,TX))
Posts: 382
indieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

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   Spotlight this post!  
Unread 06-11-2014, 16:01
roystur44's Avatar
roystur44 roystur44 is offline
Mentor/Sponsor
AKA: Roy Dumlao
FRC #4543 (Apollo Robotics)
Team Role: Mentor
 
Join Date: Mar 2010
Rookie Year: 2006
Location: San Jose,California
Posts: 363
roystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond reputeroystur44 has a reputation beyond repute
Send a message via Yahoo to roystur44
Re: Non-Robotics: VBA for Excel?

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/u...in-excel-2007/
__________________
Roy Dumlao

Mentor/Sponsor
2006-2012 971
2013-2017 4543
  #7   Spotlight this post!  
Unread 06-11-2014, 16:30
indieFan indieFan is offline
RoboDox and LVHS - Missing you!
FRC #5941
Team Role: Engineer
 
Join Date: Jan 2002
Rookie Year: 2001
Location: Seattle (was SoCal, then SA,TX))
Posts: 382
indieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

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   Spotlight this post!  
Unread 06-11-2014, 19:34
indieFan indieFan is offline
RoboDox and LVHS - Missing you!
FRC #5941
Team Role: Engineer
 
Join Date: Jan 2002
Rookie Year: 2001
Location: Seattle (was SoCal, then SA,TX))
Posts: 382
indieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

If a workbook is minimized in Excel, is it no longer considered active?
  #9   Spotlight this post!  
Unread 06-11-2014, 21:10
EricH's Avatar
EricH EricH is offline
New year, new team
FRC #1197 (Torbots)
Team Role: Engineer
 
Join Date: Jan 2005
Rookie Year: 2003
Location: SoCal
Posts: 19,825
EricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond reputeEricH has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

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.
__________________
Past teams:
2003-2007: FRC0330 BeachBots
2008: FRC1135 Shmoebotics
2012: FRC4046 Schroedinger's Dragons

"Rockets are tricky..."--Elon Musk

  #10   Spotlight this post!  
Unread 07-11-2014, 09:59
indieFan indieFan is offline
RoboDox and LVHS - Missing you!
FRC #5941
Team Role: Engineer
 
Join Date: Jan 2002
Rookie Year: 2001
Location: Seattle (was SoCal, then SA,TX))
Posts: 382
indieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond reputeindieFan has a reputation beyond repute
Re: Non-Robotics: VBA for Excel?

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.
Closed Thread


Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -5. The time now is 03:00.

The Chief Delphi Forums are sponsored by Innovation First International, Inc.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © Chief Delphi