Verify That SSIS Package Ran Successfully After Running VBA Macro From Excel?
Jan 7, 2014
I have an Excel VBA macro which calls a stored procedure. The stored procedure calls a SQL agent, which in turn calls an SSIS package. All I need from the Excel VBA code is to ensure that the SSIS package ran successfully. How do you develop a VBA code to let the user know that the job either failed or succeeded after the user executes the Excel macro? Also, is there a need for a recordset in the VBA code?
View 2 Replies
ADVERTISEMENT
Dec 21, 2006
I keep getting a compile error saying that my argument is not optional, stopping on FindFolder. I am using the following to verify that a folder named with the current Fiscal Year exists before my script runs. If the folder does not exist then it needs to be created.
_____________________________________________________
Private Sub CommandButton1_Click()
CurMonth = UserForm1.ComboBox1.Value
Fyear = UserForm1.TextBox2.Value
' Prevents script from running if no Fiscal Year is entered
' or month is chosen
If CurMonth = "" Then
Dialog1
ElseIf Fyear = "" Then
Dialog2..............
View 9 Replies
View Related
Mar 20, 2014
I have developed a model for a client which keeps crashing after being used a few times. The crash is major! Excel shuts down and asks to send an error report.
This can happen after being run anywhere from 3 to 20 times. (Clicking a POST button which transfers items to a "database" type sheet).
I developed the model on a PC using Excel 2010. When this was loaded on a Mac, the user tells me that it happened periodically, and I have seen is occasionally on my PC as well.
However, the workbook has now been loaded on a server, with users across the state of Victoria accessing via Sharepoint - most using Excel 2007. Now it is happening very frequently.
I don't think the macro is at fault, it doesn't go into debug. Excel just seems to GIVE UP.
Have you seen this kind of thing before? Is there a way of "flushing buffers" or something so that Excel can keep working?
I am reluctant to post the model because it is full of personnel details, and is 2.5mb.
View 6 Replies
View Related
Nov 13, 2006
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
does anyone know how this can be accomplished?
here is the ....
View 9 Replies
View Related
Feb 28, 2009
I have several similar documents open. They each have a macro with the shortcut CTRL + SHFT + T. The problem is each macro is different for each book. When I am working on book X, I don't want macro from book Z to run, but it does.
I need all of them to be open.
View 9 Replies
View Related
Mar 18, 2014
A coworker just ran something by me that i'm quite frankly baffled by. After running a macro, the undo function becomes disabled up to the point of when the macro was ran. I've done a quick search through the forums and can't seem to find this question so i thought i'd ask.
Is there an option to toggle somewhere or 3rd party addon that keeps Undo enabled after running a macro? On a similar topic for my own curiosity, what's the reason that it disables?
View 3 Replies
View Related
Mar 13, 2008
I have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.
View 9 Replies
View Related
Feb 3, 2013
Running Windows 7, Office/Excel 2007
I have a macro which performs some conditional formatting on a worksheet. Based on the value in cells in one column, if the value meets the criteria some simple formatting is performed (row is colored, font changes for that row etc).
The macro then calls a second macro which performs the same evaluation on the same cells from the same column. If the value meets the criteria (same as previously) it is supposed to change the value of the cell by 1/2.
Both macros work, however the 2nd macro also seems to remove all of the formatting, font changes, coloring etc that occurred from the first macro. I have had the first macro save the active workbook and close and have the 2nd macro re-open the file but it sill overwrites the formatting. If I do not have the 2nd macro run the format changes are saved. I have tried saving the file after the first macro runs with .xls and .xlsx extension and no luck.
View 3 Replies
View Related
Jun 11, 2014
I managed to implement a macro that adds a time/date stamp in Column A when any of the subsequent cells in that row are changed. With this now working, I really need the ability to restore the undo history in case a change to the data needs to be reverted.
Sub procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If c.Column > 1 And c.Column < 21 Then
Cells(c.Row, 1) = Now
End If
Next c
Application.EnableEvents = True
End Sub
View 2 Replies
View Related
Apr 6, 2013
I have a workbook with numerous macros in it and they are assigned to buttons in the different worksheets. I am trying to record a new macro and when I start recording and click on one of the other macros nothing happens. This wasn't a problem in excel 2003.
View 2 Replies
View Related
Jul 8, 2014
I have a VBS script that runs a macro in a workbook that is located in a SharePoint doc lib. The code looks like this:
[Code]....
I use code exactly like this to run other queries and it works great, but for some reason when I run this macro the instance of Excel doesn't end after the script is done running. I can see EXCEL.EXE sitting in the Task Manager and I have to end it manually.
Here is the code for the macro and related macros:
This macro just calls my other macros. Each terrslicersXX macro is a different sales territory for which there are slicers to filter out data.
[Code]....
An example of one of the sales territory slicers. Basically it just sets the slicer for the required sales territory so the SaveAsWebpage macro can save the information off as a webpage.
[Code]....
Saves the current data as a webpage.
[Code] ....
I have a feeling it has something to do with the last macro and how I publish the sheet as a webpage but I still can't get it to work.
View 8 Replies
View Related
Aug 9, 2012
I am getting a "Run time error 5", "invalid procedure call or arguement" when I run a macro on a PC other tha the one on which it was created.
In this case both PC's are running on the version 2007 of Excel.
This is the highlighted statement when the macro fails
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"SOCX25!R1C1:R" & Lr & "C23", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="SOCX25!R7C25", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
View 4 Replies
View Related
Dec 12, 2013
I am using Excel2010.
My sheet displays values collected from a database via an AddIn. The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute. When the values exceed a certain limit, a sound is played from a custom Alarm function.
This all works as long as the excel window is active.
But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.
View 6 Replies
View Related
Jun 25, 2008
This seems easy in theory but it's above my head. I have written code to add text and numbers from a userform to certain cells on a worksheet. Now, is it possible to display a message box after the code executes to acknowledge the code has executed successfully? I have tried simply adding the Msgbox function at the end of the code but that does not work.
View 2 Replies
View Related
Mar 8, 2014
I experience "out of memory" error after previous vba code is successfully finished. So quick "solution" is to restart Excel.
My current PC configuration:
HP Compaq dx7300 Microtower
Intel(R) Core(TM)2 CPU
6320 @ 1.86GHz
1.58 GHz, 1,99 GB of RAM
View 3 Replies
View Related
Nov 23, 2009
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6
Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................
View 5 Replies
View Related
Jun 1, 2009
if there's a way I can verify that a custom add-in is active? My company uses an add-in to allow end users to construct several types of API calls. My program depends on that add-in to be installed. I'd like to try to detect the add-in and halt the program if it's not there.
View 3 Replies
View Related
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
View Related
Jan 7, 2009
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
View 9 Replies
View Related
Feb 6, 2013
I worked out the code below. I supposed to check the date entered in the text box, if that date is greater than the date in cell A2, is supposed to pop up a message box, but is only half working.
For example if I enter 3/1/2013, the code works, but if I enter 1/1/2014 it will not work, it thinks the date is OK and goes on to to the Else statement. In other words, is only looking at the first part (month and day) of my date and ignoring the fact that the year is in the future.
VB:
Dim frmDate As String
Dim toDate As String
Set ws = Sheets("Dashboard")
frmDate = frmEnterDate.txtReportEffectiveDate.Value
toDate = frmEnterDate.txtReportEffectiveLastDay.Value
[Code].....
View 2 Replies
View Related
Feb 22, 2014
I found some username and password login code that I am editing for my needs, but I am having some trouble with it. I keep getting run time error '448': Named argument not found on the following line:
MatchCase:=False, SearchFormat:=False)
[Code] .....
I have also attached my workbook.
Equipment Return Log.xlsm
View 2 Replies
View Related
Apr 18, 2009
I have a code to verify the user name and password, what I want is counting the times the user enters wrong password and show a msgbox when he enters three continuous wrong passwords:
I have created the following lines which are part of a long code, the code is actually running without errors, but it is not showing the msgbox,
View 5 Replies
View Related
Jul 17, 2012
Anyway, I am basically done with a program which looks for one condition and displays an email to send as a reminder.
What I need is to have an if/then statement which checks a column in the spreadsheet and if the cell in the column states "closed" then an email is NOT displayed/sent. It seems whenever I run the macro, an email appears even if the cell for that row reads "closed."
My if/then statement in question lies following my note " 'here lies my current dilemma to solve"
Code:
Sub ThreeDayEmailTest()
'
' ThreeDayEmailTest Macro
[Code]....
View 6 Replies
View Related
Jan 7, 2010
I want to do is have a formula that will return a result if the cell contains a full stop "." and the at symbol "@". Something like a countif contains, but I'm struggling to find a solution where it contains both symbols.
I'd like the formula to return a 1 it finds both symbols, and a zero if it doesn't find both symbols.
View 9 Replies
View Related
May 1, 2007
I have an Excel VBA routine which opens another Excel file as a part of the routing. For example:
' Beginning of the code.
Workbooks.OpenText Filename:= Range("log_file").Value, Origin:=xlWindows
' Rest of the code.
The problem I run into is when the user does not have access (via Windows Active Directory) to the file located in the "log_file" range. They usually will get the old runtime error 1004. I can easily trap that error but err 1004 is used so many other times, I don't want to mistakenly trap it as something else. Does anyone know of a way to verify and/or check if the user has read/write access to a file (or folder) before opening it?
View 4 Replies
View Related
Oct 10, 2007
I need to PAUSE my VBA code (about halfway through) in order to verify some calculations before I can allow the macro to finish running. If the numbers are correct, then I'd like the macro to finish running accordingly. However, if the numbers are incorrect, I need to be able to search my Excel file for the error (the error would be due to a format issue with the data that I pulled in), fix the error, and then let the macro finish running from the point it was paused.
Right now, I've got a Message Box that pops up asking me if the numbers are right and I have to answer YES or NO. I always answer NO so that the macro crashes -- this allows me to check the excel file without having to stop the macro. Once I've fixed the data problem, then I go back into my code and let it run to its conclusion.
View 6 Replies
View Related
Apr 11, 2012
I have an Excel document with about 40+ tabs that represent different categories of data that I input on different days. I was just wondering if it was possible to have another tab (in the same document) that could accumulate all the data from the other tabs into one sheet so I can see all the data for each day regardless of which sheet it is on?
For example, what I input into Sheet 1 also gets written in the "accumulated" tab and the same for other sheets? And if I delete something from one of my sheets, it also gets deleted on the "accumulated" tab.
View 5 Replies
View Related
May 23, 2014
I am trying to create a log-in page on a userform, frmlogin in "sheet3." The userform is simply a textbox, Username (txtuser) and a textbox, Password (txtpass). Command Button is cmdcheck
I want the User to enter their username and password and for my macro to verify that they match referencing data in a seperate worksheet, (Sheet6).
Upon verification I want the user to be unloaded on "sheet1"
I am currently getting this message: "unable to get vlookup property of the worksheetfunction class"
VB:
Private Sub cmdcheck_Click()
Dim Username As String
Username = txtuser.Text
[Code]....
View 9 Replies
View Related
Jul 25, 2014
I am looking to use the formula's to determine if the contents of a cell, match or meet a given formula.
In this case, 3 alpha + 3 numeric, e.g. ABC123.
I would want it to return a TRUE or FALSE.
I've done it the long way by tearing apart into the 1st through 6th characters, but would enjoy a more succinct method.
View 6 Replies
View Related
May 10, 2012
How to use VB to get the name off the CAC card to verify their identity?
View 1 Replies
View Related
