|12 Dec 2013||#1|
Why does my VBA macro end after opening file?
I made a macro 5 years ago that I use everyday at work to compile reports. A couple of years ago, I copied the macro and made minor modifications for a very similar project. Both worked fine until I got a new PC with Windows 7. Now, my first macro just stops (without any error message) when it reaches the line to open my report file (it opens the file but doesn't continue). However, if I add stop points (maroon bullets) on the faulty line of code and the following line, I can manually make the macro continue on. While the second macro, with the similar lines of code works just find and doesn't stop or require stop points to continue.
Here is a portion of the code of the first macro that stops:
''' some code before ''' ''' Creates the reports '' Copies and pastes the data in the XLSX report file LastCol = [F1].End(xlToRight).Column Range(Cells(2, 1), Cells(LastRow, LastCol)).Select Selection.Copy xlFileName = "P:\folder\Clients\folder\Rapports\20" & CurrentYear & " - " & FullMonth & "\projet1_20" & CurrentYear & FullMonth & ".xlsx" xlFile = "projet1_20" & CurrentYear & FullMonth & ".xlsx" Workbooks.Open FileName:=xlFileName ' <------- ***THIS IS THE LAST LINE THE MACRO EXECUTES AND THEN STOPS. *** Sheets("TANGUAY_IVR1").Select If Cells(2, 1) <> "" Then Cells(1, 1).End(xlDown).Select ActiveCell.Offset(1, 0).Select Else Cells(2, 1).Select End If Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheets(FullMonth).Select Application.Workbooks(xlFile).Save ''' some code after '''
''' some code before ''' '' Copies and pastes the data in the XLSX report file Range(Cells(1, 1), Cells(LastRow, LastCol)).Select Selection.Copy xlFileName = "P:\folder\Clients\folder\Rapports\20" & CurrentYear & " - " & FullMonth & "\BEMECO_IVR1_20" & CurrentYear & FullMonth & ".xlsx" xlFile = "BEMECO_IVR1_20" & CurrentYear & FullMonth & ".xlsx" Workbooks.Open FileName:=xlFileName ' <------- ***THE MACRO DOESN'T STOP HERE LIKE THE PREVIOUS ONE, IT CONTINUES ON. *** Sheets("BEMECO_IVR1").Select If Cells(2, 1) <> "" Then Cells(1, 1).End(xlDown).Select ActiveCell.Offset(1, 0).Select RowSelect = ActiveCell.Row Else Cells(2, 1).Select RowSelect = 2 End If Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False LastRow = [A1].End(xlDown).Row Cells(RowSelect, 1).Select ''' some code after '''
|My System Specs|
|09 Jan 2014||#6|
Are you declaring variables ?
Have you declared Option Explicit ?
If not it will be impossible to trace the source of the problem.
Have you got Error capture turned on in VBEditor. ?
He already did (using breakpoints). I think it's problem with which window has the focus
On the other hand, I have gained more info on the issue. If I am working from home and connect to my work computer via TeamViewer, now both macros have more bugs. They pass through the line of code SELECTION.COPY but nothing is actually copied and in the clipboard. So when it's time to paste, the macro generates an error. Now for some reason, this leads me to believe that it's due to using computer resources to run TeamViewer and for some reason my macro doesn't like that. I don't get it though, my previous PC (that was on XP) was far less powerful and these macros were never an issue. My new PC is Windows 7, Intel Xenon CPU E5-1620 0 @3.60GHz with 16 GB of RAM.
|My System Specs|
|09 Jan 2014||#7|
And recently I made changes to other macros, which are all part of the PERSONAL.XLSA file, and updated the file on my colleagues' PCs. I just tested them and they all jam at the same place, Windows XP and Windows 7. I even plugged in my old PC and tried, now it jams at the same place. It's not an issue of system resources. There is definitely something going on in the macro. I'm starting to think corrupted file... ?
|My System Specs|
|09 Jan 2014||#8|
Option Explicit statement at top of each module makes error tracking easier during development.
Code complies faster too.
Variables are best declared as data types
Dim var As String (words and text)
Dim var As Integer (non decimal numerals -32,768 to 32,767)
Dim var (implies a "Variant" variable, which can be anything object or data)
When reading data cells to a variable VBA has to make assumptions about the data type.
Without data-type declarations, sometimes VBA throws a curve ball, and at times identifying the cause can be a challenge, particularly with some obscure methods based in unavailable Reference libraries.
Cell Text and Value are not the same.
eg, 3.00 might not be 3 nor "3.00" ... all subject to cell formatting and variable data types
Without more code to view I can only guess.
My VBEditor settings
It is so many years since I needed to touch these settings I have forgotten about "state loss".
Enable it and see what transpires, I write code with it "disabled"
1 Workbooks.Open FileName:=xlFileName ' <------- ***THIS IS THE LAST LINE THE MACRO EXECUTES AND THEN STOPS. *** 2 Sheets("BEMECO_IVR1").Select
- I would programaticaly first check
• if the book exists
• is it already open
• if not - open the workbook
• activate the workbook
• check if the worksheet exists
• activate the sheet (if you want to watch progress)
The open workbook can be assigned to a workbook object (but not if it is closed)
The target worksheet can be assigned to a worksheet object in the workbook object
NB: Sheets(<name/index>) refers to the active workbook.
Line #2 assumes the ActiveWorkbook (not necessarily the book you just opened)
A strong contender especially with VBA.
As modules are edited, code enabled, disabled, commented -- VBA bloats.
Some of my larger VBA projects became unstable/corrupted after a lot of code changes.
(Maybe why MS have dumped VBA after Office 2010)
I got fed up with manually rebuilding VBA projects (Excel) so I have written a Rescue/rebuild addin. (Excel 2003)
Some of my projects (xls, xlt, xla) have sized down to 60% of the original after rebuilding a project.
(My RSQ.xla has options to also strip out all full-line comments, which can really downsize a project)
|My System Specs|
|10 Jan 2014||#9|
In an attempt to determine if a corrupt file could be the cause, I created a brand new PERSONAL.XLSA and copied my macros in VB. That didn't fix the issue so I ruled out corrupted file.
Then I decided to separate my macros in different modules as to alleviate the amount of code in a single module. That didn't fix the issue so I ruled out module being too heavy.
Then I used "Step by step detailed" (not sure about the precision of my translation, but it's the F8 key) and then everything went on smoothly! The macro worked just fine!
So I tried it normally again and the issue came back. So I decided to try again executing the macro manually through the macros menu instead of my shortcut keys. And the macro worked just fine again.
Therefore the issue seems to be when I activate the macro using the shortcut keys I've set up. I searched the web to see if my shortcut keys affected anything (CTRL + SHIFT + D) and found a place listing all Excel shortcuts. I tried a different combo not used in Excel (CTRL + SHIFT + E) but that doesn't work either. So I looked for a simpler combo shortcut that isn't used in Excel (CTRL + e) and then it worked. Problem solved. Can't believe it was this stupid. And the even more surprising fact, is that my second macro that works fine was also executed with CTRL + SHIFT + D seeing as that shorcut actually activated a third macro which would analyze my file and only then direct to my first faulty macro or my second okay macro.
Lesson learned, don't make your shortcut combos too complicated because it confuses Mr. Excel here.
I'd like to thank everyone for your help with this issue. Especially GrayGhost with your elaborate replies. Very appreciated!
Nice day to everyone!
|My System Specs|
|10 Jan 2014||#10|
Thanks for posting back here too. It will help others. You have taught me something also.
I no how frustrating it can be ... having spent days trying to find ... what and where.
Rebuilding VBA projects by copy pasting code.
For some weird reason MS Clipboard copies bloat too.
I discovered this when problems persisted in rebuilt projects.
Then I installed a clipboard extender (ClipX - free) because I could copy multiple items as text and paste as text, without laborious back and forth with MS Clipboard between old modules and new modules.
Then one project a monster 68 modules (including classes) I just had to find an easier way.
With code I can rebuild all modules and forms in <3 seconds, manually 45 minutes ... 20 or so worksheets about 2 minutes (manually days !#! )
(I assume because MS Clipboard is generic, it copies images, files, ... everything under the sun ... it copies the underlying code that represents what we see on the module. My take on it anyway. )
|My System Specs|
|Similar help and support threads for2: Why does my VBA macro end after opening file?|
|Opening a WindowsImageBackup VHD file||Backup and Restore|
|Opening a .CDR file ?||Software|
|BSOD - Opening or attaching file using "File Open" menu||BSOD Help and Support|
|opening .car file extensions||Software|
|File opening||General Discussion|
Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd
All times are GMT -5. The time now is 05:56 AM.