Random Errors When Macro Is Run In A Bat-file
Jun 6, 2007
I'm running loads of SQL-queries in a bat-file (using the isql-command). After that has been done I want to run Excel to format the result of the queries, and save it as a excel file on a networkplace. The SQL-queries are working fine, and the macro is working fine, when run seperatly, but since I cant predict how long the SQL-queries will take I have to either give it an extra long time before I start with the Macro, or run then in a chain from the bat-file.
When I do that I usually end up with random errors in Excel. Most usually it is "Not enough memory to display completly". This error is shown before the actual workbook is loaded, and therefore no macro has been loaded either so there's nothing I can do from there.
When I click OK the macro runs as should be though, but since it should be run at 5 in the morning it will just stand there until I come into work each day, and have loads of complaint that they havent got their figures yet.
I have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.
View 6 Replies
ADVERTISEMENT
Mar 28, 2012
I have some code I'm running that works flawlessly when running it in excel. The problem arises when the generic user opens the file via a website I have running, if they chose to run the file in internet explorer rather than saving the file I get the following error:
runtime '1004' error
application-defined or object-defined error
The problem occurs in this code:
Code:
If Not Intersect(Target, Range("B2")) Is Nothing Then
Load UserForm1
UserForm1.Show vbModeless
[Code]....
I have similar code for refreshing specific pages with the "RefreshAll" being changed to "RefreshTower1" for example and I run into the same error.
View 1 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Dec 29, 2008
I want to create a Macro that will basically fix any run time errors, that should occur without users having to close the document and reopen. I know I can hit "ctrl G" in the VBA and in the immediate window type in:
Application.EnableEvents = True
and then close the VBA and the macro will work again. I just want this code to be able to be applied by my users witout having to go into the VBA. I will assign this macro to a Button. How can I do this?
View 9 Replies
View Related
Nov 29, 2006
Our Mac operators also have Excel and a problem is occuring that is causing some confusion. I have been asked to have a look but I don't use a Mac so I am even more confused than they are.
If you open an exisiting Excel file, especially one with links or where you do a lot of calculations, when you try and save the file the Mac alerts you to the fact that the file already exists and asks if you want to replace it. When you click okay it says it can't save the file because it already exists and proceeds to create another file with a name that the Mac seems to just choose at random.
Although there is a Tools menu it doesn't seem to have an Options on it so I have no idea how to change settings etc.
View 9 Replies
View Related
Aug 4, 2009
i have a column C with thousands of data. in that column, i had done a vlookup formula based on the lookup_value on its right, that is column B. some of the values arent available in the mapping table, causing the #N/A error.
i want to be able to delete those #N/A errors so that my dataset looks 'cleaner'. its tiring for me to go through each row one by one and delete them manually. i wnat to create a macro but im not so sure how to start.
in pseudo-code, it shud be something like this:
delete cell if cell_value = #N/A error.
View 9 Replies
View Related
Jan 19, 2012
I'm writing a piece of code that puts certain values in certain cells based on certain criteria. (Vague, I know.)
When the code gets to the part where it puts the first value in the first cell (in a loop), it inserts the value and then the code stops running. Even when I put in "On Error Goto Err" with a msgbox that contains the error description, the code just quietly stops.
Here is a simple macro I wrote to see if maybe the problem had anything to do with something specific to my code. This one fails too, for no apparent reason. Could my file be corrupted or something? What do I do about this?
Code:
Sub rtest()
On Error GoTo err
Sheet2.Range("p4") = "test" 'code stops on first line, whether it is this one or the next every time.
ActiveCell.Value = "test"
ActiveCell.Value = "success"
End
err:
MsgBox err.Description
End Sub
View 9 Replies
View Related
Dec 30, 2013
I have a very long (> 2 hr) macro that sifts through thousands of files and creates a table of contents (more or less). Anyway, the macro will operate just fine when I am also working at the same PC but if I walk away for an hour, Excel will sometimes generate an error (application defined or object defined error). I believe that it has something to do with the PC going idle although it really isn't going idle because I turn off sleep and hibernate while running the macro. Also, there are only two locations where the error seems to occur. See below:
Code:
Dim oApp As ObjectSet oApp = CreateObject("Shell.Application")
Dim files As New Collection
Dim fZip As Variant
For Each fZip In oApp.Namespace(zipFile).items
View 7 Replies
View Related
Mar 6, 2008
i would like an if macro to pick up if cell dest (i have used a case to define this cell) contains an error or more imoprtantly #REF! then change the offending cells to 0 and put up a message box to put "Check XTA". i have found some that i think may work but i didnt understand them (they had function in them :smask so i couldnt put them in.is there a way to put them in with out functions or could someone point me in the right direction.
View 4 Replies
View Related
May 9, 2008
how to supress the continue,end,debug, message when there is a vba error. The idea being that if there is a bug in my system that I have no realised, I don't want my end user seeing that message! I would preferrable design my own error message to appear instead.
View 2 Replies
View Related
Oct 27, 2008
I have an application that requires users to download a file from a location that is then imported at their execution into the application. I've done my best to counsel users to download the file to their desktop (for ease of locating) and to stick with a standard filename so that there are no errors. Some of these people just don't take direction well, will save the file wherever, or save the name inappropratly, or worse, open up multiple copies of the same file corrupting the name: ie: FileName.xls, FileName.xls(1), FileName1.
View 5 Replies
View Related
Nov 12, 2009
I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".
I have got the whole thing figured out and running to satisfaction, except...
The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"
Any ideas what could be casing this? The errors occur when attempting to add sheets.
View 2 Replies
View Related
Sep 4, 2007
I am getting a Runtime Error 13 Type Mismatch on some code that I am using. This macro was created to copy a range of data, prompt the user to open a specific workbook, prompt the user to select the uppermost left cell in the range, copy the data and then save and close.
Sub Copy_data9111() ...
View 3 Replies
View Related
Jul 7, 2014
I am currently working on sheet that i need to have print out user names and random passwords for as many people that entered on the sheet to a text file for email. I am running into a few issues.
1. When i try to print to a .txt file of the text from column G on sheet 'Review and Generate', all the text appears on one line. I have been trying to use the Char(10) to create line spaces, but that has not worked out for me. How do i get excel to export text from a cell to a .txt file with correct line spaces?
2. I have a random password function running on column F, however i am trying to get it only produce one result and not keep calculating random values. How do i have the cell calculate only once?
View 6 Replies
View Related
Nov 25, 2009
I have a macro that first refreshes a specific data range that imports data from a text file as such:
View 2 Replies
View Related
Aug 12, 2009
I am currently creating a database which involves using a macro to create new worksheets in the workbook. When the macro is run and a work sheet is being inserted, an input box asks the user for a worksheet name.
As you can imagine, the worksheet does not like it when the worksheet name input by the user, is the same as one already existing and so throws up a 1004 error.
In order to resolve this error I have included an error handling code to request the user to input a differnet worksheet name, as the one previously inserted exists.
My problem: It all works fine until the user types in an existing worksheet name twice, so once initially and again when the error handler has prompted a second attempt. On the second incorrect input a 1004 error warning is displayed.
I would like the error handler to keep repeating until a worksheet name that doesn't exist is inserted by the user. Is this possible?
My code so far:
Sub NewTrancheSheet()
'
Sheets("Tranche Sheet Template").Visible = True
Sheets("Tranche Sheet Template").Select
Sheets("Tranche Sheet Template").Copy Before:=Sheets(1)
Sheets("Tranche Sheet Template (2)").Select
Sheets("Tranche Sheet Template (2)").Move After:=Sheets(Sheets.Count)
Sheets("Tranche Sheet Template (2)").Select.................
View 9 Replies
View Related
Feb 3, 2014
Any way to make say cells A1 thru A10 produce random numbers from 0 to 9.
I would like to do it without a macro if possible. I also need a button to generate those random numbers. I know this button may make it impossible to not use a macro. If it has to be done with a macro then so be it.
View 12 Replies
View Related
Feb 3, 2014
Any way to make say cells A1 thru A10 produce random numbers from 0 to 9.
I would like to do it without a macro if possible. I also need a button to generate those random numbers. I know this button may make it impossible to not use a macro, but If it has to be done with a macro then so be it.
View 4 Replies
View Related
Feb 27, 2007
What I'm trying to do is as follows:
Sheet 1
A1 through A6 has STR, DEX, CON, INT, WIS and CHA. B1 through B6 is waiting for results from sheet 2.
Sheet 2
A1 is data validation of d4, d6, d8, d10, d12, d20 and d100. A2 through A7 are currently Rand formulae triggered by A1 with B1 adding number of dice (minimum 1) and B2 a modifier value add to the rolled result. D2 through D7 are data validation modifiers of -10 to +10 (0 included). E2 through E7 is the result of A2+C2. The Rand works fine, but every time data is changed Rand recalculates. I'm hoping for a macro executed by a button to generate six random numbers based on the chosen die value of A1 ranging from 1-4 to 1-100. The die roll can never be below 1 and no higher than the chosen die. I then want to transfer the result from E? To sheet 1 B1 to B6 matching its appropriate atribute. I named them Char01_STR, etc on sheet 1 A1 to A6.
View 9 Replies
View Related
Sep 18, 2009
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
View 2 Replies
View Related
Mar 18, 2009
I have a spreadsheet which has a column of 100 Poisson-distributed random numbers (lambda=2) which I generated using Data Analysis -> Random Number Generation in Excel. I want to create a macro to put these numbers in for me. When I tried recording one, it didn't work. Is there a way to do it?
View 14 Replies
View Related
Mar 31, 2014
Is it possible to add letters to this "Unique random number" generator it is very fast and takes only 5s to run on 50,000 rows, I have a different "Unique random Sequence generator" macro but it takes about 30 minutes to run on 50,000 rows
Code:
Sub generateuniquerandom()
Dim b() As Boolean, e As Range, k&, x&
Dim lRow As Long
With Sheets(1)
lRow = .Range("F" & .Rows.Count).End(xlUp).Row
[Code]...
View 3 Replies
View Related
Jul 2, 2008
i have a workbook that has several worksheets. i may create new worksheets at any time. i want to create a macro that will select a certain field of cells and copy it to a random spreadsheet of my choosing. i know how to make a macro that copies cells and pastes them to a different worksheet, i just want the macro to ask me which worksheet i want it to copy to.
View 9 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
Feb 23, 2014
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
View 1 Replies
View Related
Jul 7, 2008
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies
View Related
Feb 8, 2013
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
View 1 Replies
View Related
Mar 14, 2014
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
View 2 Replies
View Related
Jun 24, 2006
I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
View 9 Replies
View Related
Feb 21, 2014
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8
B2 to E9
C2 to F10
D2 to G11
E2 to H12
F2 to I13
View 4 Replies
View Related