Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Problem Trapping Errors Using Excel VBA

I have an Excel sheet that uses VBA to open another sheet and read data from it every 5 minutes. The logic is essentially this:

On Error goto whatever
Open the other sheet
return success

return failure

If success, reset the timer for 5 minutes.
If failure, reset the timer to try again in 30 seconds.

The reason we might not be able to open the sheet is because it is being written to periodically by another application. If we're trying to read when that's happening, we'll get an error.

This logic works fine if the other sheet we're trying to open is on a local drive. However, it needs to work with the other sheet on a network drive. The problem is, when we get a failure opening the sheet on a network drive, an Excel dialog box pops up displaying the error. (don't get this message when sheet is on a local drive) Our sheet/VBA is essentially locked up until somebody presses "OK" to close that dialog box.

Is there some error handling setting I can change to get around this problem?

I'm running Excel from Office 2007, and XP Pro.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Trapping Errors
I am working on trapping errors and displaying them in a text box. Is there a way to get the error number so i can use it to output as a string?

Or even better but prolly not possible: get the text description of the error?

Trapping API Errors
I'm using the shellexecuteinfo API call to run a few programs for my app, but if there is an error, a message appears and execution halts until OK is clicked. My VB error handler doesn't seem to pick this up.

Is there any way of stoping this from happening? I need an automated process to run overnight when I'm not around, so if the message comes up, my process stops, and everyhing goes horribly wrong for me when I get into work in the morning.

I'm running VB5 on Win 95 & Win 98.


Trapping Errors
Why isn't my error trapped? I've built a code which uses two loops. Since I don't have good conditions to restrict the number of loops I use error trapping for it. But I still get errors that stop the execution of the app (5 - Invalid procedure call or argument) although the line above the one that causes the error is 'On Error Goto EndTags'...

The code follows this format. If this doesn't help I'll post my code. Thanks.

VB Code:
Do  On Error Goto EndTags   If ...     On Error Goto EndAttributes    Do     Loop   EndAttributes:  End IfLoop EndTags:

Trapping Errors. Please Help

I am trying to execute BCP out by using the Shell function in VB.

How can I trap the error messages returned by BCP command?

I would appreciate if you guys can help me out on this.


Trapping Errors???
I'm buildning an ASP app that retrives data from an access database. I'm using ado to retrive the data.

What errors should I try to trap??

I mean what is most likley to happen???

At first I'm only going to trap errors that occurs from the data retrive....

I need some tips...


Trapping Errors
i have a form which has a button that opens a second form. this second form has an MS Agent control which basically runs a speech

my problem is that i don't want the MS agent control to be a requirement when i distribute my application. so if i clicked the
button which opens the form with the ms agent control, it will give me an error saying there's something wrong with the form i'm about to

i've used the "On Error Goto 0" statement and placed it on the LOAD event of the second form but i still can't trap the error.

any idea on how to solve this?



Trapping Errors?
Greetings everyone.

My situation:
I am in the design stage of building an app that I plan to use both MS Access and SQL MSDE with. I would like to build it in the best way to prevent data loss. I plan to use a central SQL database to store all data. The clients will be running a VB app that can read and write data from the central SQL database. If by chance the network connection goes down or the server gets restarted, I would like the app to be able to keep running, just writing the data to an Access database on the local C: drive instead. The user just gets an alert that the connection to the SQL server has been lost. They can continue to work though. When the link comes back up, the data in the access database can be synchronized to the SQL server. This synchronization can be initiated by the user.

My questions:
1: Is it best to use ON ERROR RESUME NEXT and then trap on the error messages? Is there a better way to know if the data made it to the server?

2: Say the user is writing data to SQL and the network connection drops in the middle of it. Some data has already been sent to SQL, but not all of it. Does SQL write what it has already gotten or drop it all since the connection hasn't been completed? Can I trap this with an err.number?

3: Are there any other suggestions to make this simple, faster, or easier?


Trapping Untrappable Errors?
I have data in a number of worksheets in a book. However I want to remove empty lines, which some users insert. The following code sometimes work, but often I get a message box with err.number = 1004 and err.description = "No cells returned" (or something to that nature). the error occurs in the code when the .specialcells(xlCellsTypeConstant).count = 0.
I can not trap the error. This error number is not trappable according to the trappable error help page. Is there anyway to trap the error any way. (similay to the try-catch functions in C++)?

The code is attached as text. How do I make it appear in those nice grey text boxes many of you use?


Trapping Provider Errors
From my readings, I have found that you can find Provider errors through the connection.errors collection. Is there an event that tells you that new errors have appeared in the errors collection, b/c from what I've read, ADO does not raise an error when a Provider error occurs. Therefore one cannot use the "On Error" clause.

Do I have to check the connection.errors.count value after every ADO command to see what's happening on the server end?


Trapping ActiveX Errors
I get run-time error 429 when I try to Set a non existing Excel.Application as myApp.
On Error seem doesen't work, what does?

On Error Resume Next
Set my= GetObject(, "Excel.Application")

Trapping Application Errors
ok guys,

I have some macros written in VB. The purpose of this macro can be easily understood by looking at the following code.

Sub main()

On Error GoTo Err_here

'Declare variables

'set variables

' connect to the application
' open a scheduled report
' do processing
' save or print results
' close report


'quit application
'release or free variables and set them to nothing
Exit Sub

Open "C:ErrLogFile.txt" For Append As #1
Print #1, "Error occured while running " & strRpt & "."
Print #1, "Error #:" & Err & " " & Error$
Print #1, "Error occured at " & Now() & "."
Print #1, "********** End Of Error ***********"

Close #1
Resume Exit_Here
End Sub

what i am doing here is - connecting to my reports developer(cognos impromptu) application and running the desired report.

Now i want to do some error trapping and came up with the error handling code shown above. But this just traps any errors in the variable declarations or file path declaration, BUT DOES NOT TRAP APPLICATION ERRORS.

IF THERE IS AN APPLICATION ERROR OCCURS THEN I JUST SEE A MESSAGE BOX DESCRIBING THE ERROR, UNTIL I CLICK OK ON THIS MESSAGE BOX...all my other scheduled macros stop working. Once i click on...the scheduled macros start functioning again


Thanks in advance


Trapping RunTime Errors
I'm using VB to connect to an Access database that has validation rules. When invalid data is entered to the form and written to the DB a Run Time error is produced with the Validation Text from Access as the error message. Is there anyway to trap this message and present it in a more user-friendly manner, one which won't end the program?

(I was hoping to have a MsgBox containing the message and telling them to try again)

Trapping Inet Errors???!?!?

Anyone know how to trap errors from the Inet control??

I want to trap for exampel IncorrectPassword and Incorrect user. I know how to write a errhandler, but the problem is when I try to fake an error by enter the wrong password or username it only returns the error 35754 Unable to connect to remote host.

In the object browser and in MSDN they say that incorrectpassword and incorrectusername is trapable errors...

Is this just an bug in the Inet control that one must live with or??


Trapping Runtime Errors.
OK, we all know how to trap errors:

Private Sub xyz

On Error Goto Hell


Exit Sub

Msgbox Err.Description

End Sub

Well, I am doing some stuff with the Drive List Box and Directory List Box controls. I've used them together so that the Directory List shows the contents of the selected drive in the Drive List Box:

Private Sub DriveListBox1_Change()

On Error GoTo EH

DirectoryListBox1.Path = DriveListBox1.Drive

Exit Sub

Msgbox ("Drive not available!")
DriveListBox1.Drive = Left(DirectoryListBox1.Path, 2)

End Sub

However, if I select a removable disk drive (eg floppy drive) from the drive box, that doesn't contain a disk, I get an error. Then, instead of going to the error handler (EH in the above example), the program stops running, and I get the following message box:

"Run-time error '68:
Device Unavailable"

...with the usual debug, end, etc... buttons. Very strange!

Is there a way to trap this error so that program can continue to run?

I would be really grateful if someone could help me, since this is the last hurdle before I can complete the application I am currently writing.

Thanks in advance,


Trapping Unseen Errors

I have been getting some weird errors at client sites which somehow do not come up at testing time, no matter how much I try to simulate them, e.g. 'Type Mismatch' and 'Division by Zero' for starters. It is really embarrassing because the app just terminates in the client's face.

How can one plan for and trap such errors at design time?


Experience is a bad teacher for its exams precede its lessons.

Edited by - GoodGuy on 7/12/2005 11:34:13 PM

Trapping Errors In Access In VBA
VBA against Access 2000
A simple table in Access Test with two columns id (autonumber)
                                                                          Descript (datatype Memo) zero size not allowed

In VBA I create a simple procedure

Sub InsertData()
  Dim dbs
    Set dbs = CurrentDb()
    Dim Base As String
    Dim str As String
    Base = "INSERT INTO test (" _
        + "Descript)" _
        + "VALUES ("
    str = Base
     str = str + """" + "" + """"
    str = str + ");"
    dbs.Execute str ' str equals to INSERT INTO test (Descript)VALUES ("");
End Sub

The result is that no Insert happends in my Access database (due to the fact that the 'Descrip' contains an empty string,
the test-table does not allow). I would have expected an error beeing returned.

What kind of error mechanism can be used to trap this error and make it known that no insert was performed ?

Thanks in advance

Trapping Errors In Printing
Can errors in printing be trapped in the Err object? For instance, I want to know whether the printer is powered on/off, is online/offline, or if it has paper or not. I'll be printing reports made in Crystal Reports, and I need to put a value in a Boolean variable: True if printing is successful; or False if any errors occured during the printing. My app's next process depends upon the value of this variable, so I need to get its real value. Does anyone know something about this?

Thanks in advance!

Trapping ODBC Errors
How can I examine an error code from ODBC and Oracle
using VBA?

if I use form_error, I can trap the error but dataerr gives me the code 7786 for different errors.

Trapping Errors On Shell Command

I'm using the Shell command to Map Network Drives, using NET USE. Is there any way to trap returned errors? For instance, if I'm disconnecting the network drive (Shell "Net Use E: /d") and the E: doesn't exist, in DOS the message 2250 is returned. Is there any way to get this message from the Shell command and display some relevant text to the User?

Maybe this question has been answered before, I've tried searching but haven't come up with anything so far

Problem Trapping ODBC Errors
I have a spreadsheet that calculates all the inputted values and uses VBA to insert some of it into a database (Ingres DB). As I didn't write the VBA code it is very confusing as there is a lot of it and the comments are in Swedish!

Anyway, when I run it against the database it returns the less than useful ODBC-call failed error. Now I know that it is just returning the first error in the list so I know I can loop through the errors with this code:


Dim errOdbc As Error
Dim ErrorVal As String

If DBEngine.Errors.Count > 1 Then
For Each errOdbc In DBEngine.Errors
With errOdbc
ErrorVal = .Number & " " & .Description & " " & .Source
MsgBox ErrorVal
End With
End If

Problem is I keep getting a type mismatch error on the For Each line and I cannot figure out why! Unless I am missing something fairly obvious (likely) I just cannot see it!

Anyone know why I am getting Type Mismatch on that line?

(SOLVED)Trapping Errors In A Loop...
Hi guys... I have a simple question to you...

I have the following code:

VB Code:
For x= 1 To Combo.ListCount - 2    On Error GoTo ErrSec    If Not NotErrcnxBTE Then       Set DataBTE = New ADODB.Connection     Else       DataBTE.Close       NotErrcnxBTE = False    End If    Path2 = GetDBPath(Combo.List(x))    DataBTE.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                 "Data Source=" + Path2 + ";"      DataBTE.Open    NotErrcnxBTE = True    CompDBErrSec:    Select Case Err.Number  ' Evaluate error number.       Case -2147467259               FGRacklist.TextMatrix(UsedRows, 0) = Combo.List(x)             FGRacklist.TextMatrix(UsedRows, 1) = "No Se Pudo Conectar"       Case -2147217843              FGRacklist.TextMatrix(UsedRows, 0) = ComBTE(0).List(Btecount)             FGRacklist.TextMatrix(UsedRows, 1) = "Falló Autentificación"       Case Is <> 0             MsgBox Err.Description + CStr(Err.Number), vbCritical, "Error En Form Load"    End Select    Err.ClearNext x

The problem I have is that I'm able to trap the first error... but if I get another error this one raise the 'normal' run-time error instead of getting trapped...

So, I'm wondering if there is something I can do to trap all the errors...


Trapping Errors From Business Tier DLL
I develop a three tiers application in VB6, client-server architecture.
The User interface tier is a VB6 EXE project. The Business tier is a VB6 Active X COM project (DLL) that has some classes.
The data are introduced by a VB form of User interface tier, but is validated in Business tier’s property functions.
When a data isn’t OK (wrong data), I have to write a message on the screen and retry the data input flow in the User interface tier input procedure.
My problem is:
How can Business object tier procedure to tell User interface tier procedure that there are an error (and it have to retry the data input).
I have try to use err.Raise in Business tier procedure, but User interface tier procedure isn’t interrupted by an error:
On Error goto err:
Object.member=value‘call the property function of member of object.
‘The property contains err.Raise
Msgbox “is ok”
Exit sub
Msgbox “I was interrupted by an error”
End sub
I see “is ok”.
Is this unpleasant situation related to the following sentences from MSDN Library Visual Studio 6.0 help: “System errors during calls to Windows DLL do not raise exceptions and cannot by trapped with Visual Basic Error trapping” ?
How can I trap validation errors from Business object tier procedure?

Trapping Rs.close And Db.close Errors
Hi all, I have written an application that hits a database for stuff using Access and DAO. Everything is working great, except for one thing - my error handling.

I am trying to ensure that when an rs or a db are opened, they are always closed. I am trapping errors; the problem is that in some routines, the error can occur while a db or rs is open or not! In my error handling bit, I am including the following code:

    Set rs = Nothing
    Set db = Nothing
MsgBox "There was an error checking the entries. Please try again." _
& vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Desc: " _
& Err.Description, vbCritical, "Error In GetEntries()"

I want to ensure that if an rs and db were opened, they are closed. Unfortunately, if an error occurs before an rs or db are opened, I get another error when entering the handler code. I tried using

if not rs = nothing then
    Set rs = Nothing
    Set db = Nothing
   'do nothing
end if
MsgBox "There was an error checking the entries. Please try again." _
& vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Desc: " _
& Err.Description, vbCritical, "Error In GetEntries()"

Bit it tells me that this is an invalid use of property.

How can I test if an rs or a db are open or set to nothing, so that I can better handle errors?

I sincerely appreciate any help you are able to give.


Disabling Or Trapping Short Cut Keys In Excel
any body has an idea how to disable shortcut keys in Excel by code in macro .. or reset them .. or one can take its own action on Existing hot/shortcut keys.. for example i want when i press Ctrl+F , find dialog box comes , i dont want that dialog box to come i want to over ride this Ctrl+F functionality of Excel..

from menu and command bar i have succed to over ride it but i m not able to trap short cut keys .. any one has any idea ???

Excel VBA - Trapping Sheet Change Event
Hi all,

I know this is not strictly VB, but thought someone out there might be able
to help.

I am trying to "protect" the text in certain cells in an Excel spreadsheet.
I hear everyone screaming "Lock the cell and protect the sheet". However I
want the users to be able to insert rows and columns etc. and as far as I
can see, locking cells and protecting the sheet also disables this ability
for users.
I have therefore started to look at what events I can trap.
Just for now I have put a msgbox statement in each of the following events
to check when each fire. Problem is for some reason when I update the
spreadsheet, NONE of them seem to fire (!?!).
ThisWorkbook sheet Workbook_SheetChange event
ThisWorkbook sheet Workbook_SheetSelectionChange event
Sheet11 (the one I'm working on), Worksheet_Change event
Sheet11, Worksheet_SelectionChange event

I get the feeling I can't see the wood for the trees. Does anyone have any



James W. Farquhar

Excel Error: Excel.exe Has Generated Errors...............
The whole error is this.

EXCEL.exe has generated errors and will be closed by windows.
You will need to restart the program.

An error log is being created.

This happens when I am opening an Excel spread sheet using VBA within a third party software.

What actually happens prior to this is the application using VB to open Excel and then open the Excel Spreadsheet. That all works fine but I can't make any of the cells active by clicking on them. If I touch the Scroll Bar or try to change to a different sheet within the spread sheet I get this error and have to restart Excel.

I can however once Excel is in front of the other application open the same Spread sheet in the same window and the cells become active.

Has anyone ever seen this and if so did you find a resolution?

Thanks for any input.


Excel And Errors
I can open an Excel file no problem.
I can activate a specific tab in the Excel file no problem.

I want to check for the tabs existence before I try to activate it to prevent an error occuring.

Does any body know the correct syntax?

The following works (unless the tab called 'Test Tab' does not exist);

Worksheets("Test Tab").Activate



Compile Errors In Excel
Dear all.

A user is using macro's and they work fine on one particular system.
Now the user is migrationg to another system (from W2K > XP, from excel 9.0.2720 to 10...sp3)
and the macros's wont run anymore.

When we look at the debugger we see that most macro's go into an error when functions like CurrentYear, counter are called.
We receive an message that says: "Compile error: Can't find project or library"

So it's obvious we're missing something but we are too newbie to know what.

Please HELP

BTW: as the error error occurs between OS's and excel version of the same version we know that there is something extra installed on this piece of hardware during the time but we don't know what.

Sub Sales_Benelux()

CurrentYear = Worksheets("Data").Cells(11, 9)
CY = ">=" & CurrentYear
Selection.AutoFilter Field:



For Counter = 3 To 10
Set Name = Worksheets("Data").Cells(Counter, 1)
Set CurrentMonth = Worksheets("Data").Cells(8, 9)
Set PreviousMonth = Wor

VB Errors In EXCEL 2007
A spreadsheet application written in EXCEL 2003 using the visual basic editor in EXCEL runs fine. When taking the same application to EXCEL 2007, errors relating to Range and Select occur. The same errors occur whether EXCEL 2007 is running under XP or Vista. When stepping through the program using the F8 key, the errors don't occur. Running in a normal fashion, the errors occur. The security level is set LOW and Windows Classic is the theme. Any ideas what may be happening?

Excel.exe Has Generated Errors

I was running my VB program opens multi tab sheets and convert into one sheet and finally save as csv file.

All files are generated sucessfully, But after processing every file it gives program error with below messgae

"EXCEL.exe has generated errors and will be closed by windows.
You will need to restart the program.

An error log is being created."

Is there any way to suppress these message or fix.

For source code please see my attachment.


"Upgrade" From Excel 2000 To Excel 2002 (XP) Errors
I am getting multiple error messages from a fairly extensive Excel VBA application developed under Excel 2000 (on Windows 2000) when it's run on Excel 2002 (on XP).

Error messages include Run-time Error 1004 when trying to run the code:

Sheets("all_mine").Range("J1:J2").Interior.ColorIndex = x1None

I'm told there are other errors too.

Where can I find a list of the non-backwards compatibility of Excel 2002 VBA functions? (I don't have XP myself - other users do).

Any ideas?

Help With SQL To Excel Export File Saving Errors
I have a sub routine that will export the contents of a SQL table to an Excel workbook. It is called when the Save File As button is clicked, which opens a Save common dialog so the user can enter the path and filename. This is stored in the FileToSave variable which is passed into the subroutine along with the SQL query string

Then, the routine opens objects and connections for ADO SQL, gets the recordset, and then opens objects for Excel automation. Then it writes the contents of the recordset row by row to the excel sheet. All fine and dandy. However, problems start happening at the code line that actually saves the file when exporting is finished.

The first error I had occurs when the user chooses a filename that already exists AND that file is currently open. It would cause a run-time error '1004' Cannot Access file. So I added the 'On Error goto OpenFileError' handler that displays a message to close the file, then click OK, and it would resume at the save file line again.

So the user closes the file and the line doesnt cause an error the second time through, but an Excel prompt comes up: 'File already exists. Would you like to overwrite?' with 'Yes, No, or Cancel' buttons. Also, this excel prompt is displayed when the user selects an exisiting filename and the file IS NOT currently open.

Now, the routine works with desired reuslts when the user chooses the 'Yes' overwrite button, but selecting 'No' or 'Cancel' causes the Excel prompt to go away and then first message 'File in use, close the Excel file, and click OK' reappears. The file isnt really open this time, the program is just interpretting this MS Excel Conflict Resolution dialog as another error(I think), so it jumps to the error handling code again. So an endless loop will occur unless the user chooses to overwrite.

So, is there a way using the Save Common Dialog box upon the user choosing a path and filename to check if the file already exists, and prompts the user to overwrite at that moment, before passing the filename to the export routine? Then i could set the xlapp.Workbooks.SaveAs code to 'always overwrite', thus getting rid of having excel worry about this conflict, thus removing this second error prompt(which i cant find a way to address through code). Any other suggestions to a better way of doin this are greatly appreciated.

'Button Code to save Master File
Private Sub buttonSaveMaster_Click()
Dim xlFileName As String
Dim SQL As String

diSave.DialogTitle = "Save"
diSave.Filter = "Excel Files (*.xls)|*.xls"

xlFileName = diSave.FileName

SQL = "select * from Master"
Call CreateSpreadsheetFromSQL(SQL, xlFileName)
End Sub

'Routine to Save Master File
Public Sub CreateSpreadsheetFromSQL(strSQL As String, FileToSave As String)
Dim xlApp As Object
Dim xlSheet As Object
Dim xlRng As Object
Dim rs As Object
Dim dbConn As ADODB.Connection
Dim stConn As String
Dim vntRecords As Variant
Dim vntRecordsTemp As Variant
Dim cntRows As Long, cntFields As Long

'Set up the recordset
Set dbConn = New ADODB.Connection
dbConn.Open stConn
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, dbConn
vntRecords = rs.GetRows

'Set up the Excel objects
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)

'Fill in the spreadsheet
Lots of Excel automation code here...

On Error GoTo OpenFileError

xlApp.Workbooks(1).SaveAs FileToSave, xlNormal '<---this is error line

Set xlApp = Nothing

Set xlRng = Nothing
Set xlSheet = Nothing
If Not (rs Is Nothing) Then rs.Close
Set rs = Nothing

Exit Sub

MsgBox "File is currently open. Please close Excel before clicking OK.", vbExclamation, "Error"
Resume Retry
End Sub

Copy Paste Errors In VBA Excel Code&lt;==PLEASE HELP
I copy and paste 2 seperate times in my VBA code. I run my code and get this error message at the first PasteSpecial Instance: "PasteSpecial method of Range class failed" If I run it again the compiler stops at the second instance of my PasteSpecial code. Then if I run it a third time my code works fine. Then it continues again on the next time I run the code.

Do I have to free memory or something why do these errors seem to happen only once?

Check If Excel Sheet Has Errors In Cells
Hi All

Does anyone know the correct way to check if there is an error in an Excel worksheet?

Code:'e.g. Assign a variable to the value in Cell A1 of a worksheet

strValue = XLRange.Value

'Will work fine unless there's an error in the cell (e.g. #DIV/0!)
'where you'd get a Type Mismatch error

Could do On Error Resume Next before each line but I've got lots of them and I think it might make the code look messy.



Weird Excel File Behavior {automation Causes #NAME Errors}
i have a worksheet excel file that has data for a chart within the same workbook, before running my program (which sets up a few settings like disabling auto filter, unhide row/column, unprotect workbook/worksheet, setup page orientation, setup print order and other settings), the data are fine but when my program processed the file most of the cells has #NAME? but when i looked on the formula bar the formula is the same, i didnt place any new data when processing the file such formula is like this:
before processed by my setup program it has a value why did it change and what to do about it. By the way because of the #NAME? behavior the chart connected to this data became blank. Only the lines are there and labels, the info is not there. I need your help, kinda new in this kind of behavior in excel

Thanks in advance people

Sending Data Fom Excel To Another Program, How To Control Errors?

I'm looking for help on how to control errors in my program & couple of other things.

This is a vb6 program I use at work. I had a friend mine write it since i'm a novice. Basically this program reads specifically formatted excel spreadsheets and sends data from them to an active window. Before sending the data, this program cross references an access database. The program is not very neat, but it does the job.
You could actually modify this to do other stuff too...

At the url above (or attached), you can see the code i'm using, along with a sample spreadsheet and database.


First this program checks for an active window "Inventory Adjustment". If it's not found, it displays an error. I would like it to exit if it finds the error, instead it displays a run time error 91.

Then the program launches the form, in which you select the spreadsheet you want to work with and you must TYPE IN the sheet name. If the sheet name does not exist, there is a runtime error 9 "Subscript out of range". Actually I would like it to diplay a msg box then exit or loop back to the beginning. Also a problem with this ... if the sheet name is not found, excel.exe is still open even though the program errored out.

Another issue I have with this program: sometimes, I get a runtime error 5 - if I restart the program it's fine...not sure what's wrong here...

Since i'm asking... another issue is this:
I'm basically sending data blindly to a specific active window. I'm sending the data then how ever many enter keys I need to goto the next field. In the {Item} variable, there is a max of 15 characters. If the max characters is reached, I don't want to send the enter key. Well in the vb program it's truncating everything to 15 characters. This is ok for the {Item} variable, but the {To Job} variable has 16 characters on occasion. The {To Job} field has a max of 22 characters (which no job is that long). As the program stands now, if the {To Job} variable has exactly 15 characters, the enter key is not sent after the variable, when it should be. So basically I need to change the program from truncating everything to 15 characters to just look at the {item} variable....(if it's 15 characters, don't send the enter key after that variable.

If anyone can help with this, I would appreciate it.

Also, if you're interested... I have a different version of the code that allows you to select the starting/ending row and columns.

Why the program was created:

I work for an electrical contractor company. We use a database program called "ComputerEase". The spreadsheets contain material codes and qty info (basically what type of material will go into a specific floorplan of a house). I needed this program because otherwise I would have to print and manually type all day long. Now I just run this program..... I use the program on some sheets over and over, I just change one variable that I imput into the form.

Edited by - cyberfreak-vegas on 5/7/2003 11:51:40 AM

Newbie - Macros Work In Excel 2000 But Some Errors On 2002
Hi All

I have a vb macro that retreives data from an oracle server via a jsp servlet. I use vb macros to display it in an excel template. This works perfectly fine for excel 2000 as it was compiled using 2000. But once i load the sheet on excel 2002, the template looks fine but the output is somewhat screwed and some functions dont work. I am using the excel 10.0 object library.
what should i do?

Capture Warnings OrAlerts In VBA For Excel (similar To Capturing Errors)
Hi, I have a question that I hope is simple but I cannot figure out for the life of me. I am writing the block of code below which has an error handler in it to capture any errors and log them to a string. The issue I am having is that I want to capture a WARNING or rather an ALERT. Because excel is not treating the Alert or warning as an error I cannot capture it. Can someone please help me!!!

Public Sub Retrieve_CF(Range_Refresh As String)
    Dim setSheetOption As Long
    On Error GoTo Retrieve_Err

'Sheet1.Activate 'Activate the sheet where the report is located. The the name of the sheet is not sheet1 it can be     'referenced as such because it is the first sheet in the report
    'Essbase function that is used to retrieve data from the current sheet and highlights
    'the range in the sheet that needs to be refreshed

    setSheetOption = EssVSetSheetOption(ActiveWorkbook.ActiveSheet.Name, 13, True) 'Use Alias'
    setSheetOption = EssVSetSheetOption(ActiveWorkbook.ActiveSheet.Name, 11, True) 'Retain Formulas
    setSheetOption = EssVSetSheetOption(ActiveWorkbook.ActiveSheet.Name, 5, 1) 'No indentation
    Retrieval = EssVRetrieve(ActiveWorkbook.ActiveSheet.Name, range(Range_Refresh), 1) 'Retrieve Data from specified     range of cells

    Worksheets(ActiveWorkbook.ActiveSheet.Name).range("a1").Select 'puts cursor in the upper left corner of the report
    Exit Sub

    ErrorString = ErrorString & "Error: " & Err.Description & " #" & Err.Number & " Retrieval area:" & Range_Refresh & vbCr
    Resume Next

End Sub

The issue I am having is that I want to capture a WARNING or rather an ALERT. Because excel is not treating the Alert or warning as an error I cannot capture it. Can someone please help me!!!

Force Errors/Test For Errors True Dbgrid
Hi all,

Anyone know how i can test for errors on true dbgrid??

I.e i want it to come up with errors not trap it!!


ARGH! I Am Getting Errors Without Any Errors! ?
In my Form_Load I have

On Error GoTo ErrorHandler
And it completes the whole Form_Load and then at the very end it runs the ErrorHandler section.

I tried printing Err.Number, Description, etc... and its all blank?

What gives? and how can I find out EXACTLY where this error is happening?


Trapping The TAB Key
What are some of the best ways to trap any pressing of the TAB key? I have been utilizing the "lost-focus" of objects in order to determine if the TAB has been pressed. I sure that I am overlooking a better means of trapping the silly key that has its way of "hiding" from VB.

Any help will be appreciated.

Key Trapping
Is there any way to trap the Pgup, PgDn, arrow keys, etc. like the old
inkey$ function?


Key Trapping
Is there a way to trap a specific key from everywhere inside and outside of the application without any special control like HotKey or something like that ?

Help! I Need Help On Trapping
What is the eaiet way(s) of trapping keystrokes with the keypress/keydown method???

?¿-=The Bomb=-¿?
"You all stare, but you'll never see; theres something inside me!"

Trapping ESC Key
I want to minimize a form on the press of Escape Key. How do I trap the Escape key has been pressed... Pls help.. seems ideas escaping from mind

Key Trapping
Does anybody know how to use this fucntion? i have tried searching for it on google but can't find anything. I basically want to prevent users from typing in certain characters within a group of text fields, i.e. letters, and characters such as $%^& etc. etc.

Trapping The F1 Key
How can I trap the F1 key so it doesn't go looking for a help file?
I have the form's KeyPreview = true...

Trapping The Tab Key
In my app I have put a RTF box. I would like to be able to use the tab key to indent but when I use the tab key I move to the next tab stop. How do I cancel the tab event or how do I write code to use the tab for indenting a line?

Trapping The Tab Key...
I'd like to be able to detect if the tab key is pressed and perform my own event instead of going to the next object in the tab index.

I've tried using the keypress event -- I don't see an "ascii 9" or a "vbKeyTab"

The KeyUp and KeyDown events say specifically that they're not triggered for a tab event...

Any ideas?


Copyright © 2005-08, All rights reserved