I always preference my VBA questions in that I am novice at best but I do know how to work the debugger and I can fix some code when the time comes. Our business is heavy on Excel and VBA and I doubt we will ever get away from it until I can balance software development with my other duties as the one IT Admin. We have a spreadsheet that two weeks ago (last time it was used) that worked and today, it doesn't. When running a macro, it get's an Overflow: Runtime Error 6 Overflow And it is in this block of code: I know contextually, it will be hard to troubleshoot but through the debugger, this is where it stops for sure, specifically at the pos = pos +1 portion.
Error: Overflow - when exporting or merging queries. Search Knowledgebase. Overflow errors can occur when exporting or merging queries in Query. Some of the errors that occur are. (Excel does not recognize any dates earlier than this). Select a different export format, such as the CSV (comma separated value) format for the export.
This sheet hasn't had anything done to it from a macro point of view. It happens on my PC and the user's PC.
Any quick tips on what causes Overflows specifically? And what would cause it to fail today as compared to 2 weeks ago with no edits to the VBA code? KnoxvilleChuckster wrote: Ok it looks like the calling code is passing the string 'File Not Found'. I'm wondering if one of the worksheets in this setup have been moved or a computer is offline? I agree that you may want to post the code that calls this function.
As a reply to my response bellow too, I manually copied that file over and I run the sheet as it was last saved with START date of 7/8/2013 and end date of 7/29/2013 and just run the Macro, it works.so that resolves the FILE NOT FOUND ERROR. Now, if I put in any start date AFTER 7/16/2013, I get the overflow, regardless of the END DATE.anything before 7/16 works.anything 7/16 or later gets an OVERFLOW.
When I debug, the ARG variable shows MCBI DEL as a date. Looks like someone made a typo on the date in the data sheet and it is fixed. I do appreciate your help as this forced me to get more comfortable with debugging.I really didn't discover the 'hover over' option until earlier this year.it really helps!
This is odd; I suspect the calling code is passing a string that has no non-zero characters in it, so the pos integer gets incremented until the mid function fails, or pos overflows. You could add this line of code below 'Dim pos As Integer': OPEN 'C: Temp debuglog.txt' for output as #1:print #1, arg:close #1 Next time you get the overflow, look to see what arg was passed by opening this file (C: Temp debuglog.txt). If the value in the file is unexpected, you'll need to look at the code that called the function to find out why it passed an unacceptable value. The above line of code assumes you have a Temp folder on your C: drive; change the code if you need to use a different drive and/or folder. Here's some more info: My workstation (windows 8 pro) may not be the best one to test this with.
The aforementioned FILE NOT FOUND is because this line of code: Call apiCopyFile(' SERVERNAME Applications Schedule Cabot-s Cabot, S.xls', 'C: Cabot, S.xls', 0) I don't think it has the permissions to copy it to my C drive as it never copies it over. But on the users workstation in question, it does copy it over. I am about to fire up an XP test virtual (user is on XP) and troubleshoot from there and see what happens in that ARG value. KnoxvilleChuckster wrote: Ok it looks like the calling code is passing the string 'File Not Found'.
I'm wondering if one of the worksheets in this setup have been moved or a computer is offline? I agree that you may want to post the code that calls this function. As a reply to my response bellow too, I manually copied that file over and I run the sheet as it was last saved with START date of 7/8/2013 and end date of 7/29/2013 and just run the Macro, it works.so that resolves the FILE NOT FOUND ERROR.
Now, if I put in any start date AFTER 7/16/2013, I get the overflow, regardless of the END DATE.anything before 7/16 works.anything 7/16 or later gets an OVERFLOW. When I debug, the ARG variable shows MCBI DEL as a date. Looks like someone made a typo on the date in the data sheet and it is fixed. I do appreciate your help as this forced me to get more comfortable with debugging.I really didn't discover the 'hover over' option until earlier this year.it really helps! Hi all I am new to VBA.
And I wanted daily email from the same company to be saved to a shared folder with auto created today folder. D: Testing 2014-10-07.But the VBA code does not work.