File Cannot Be Accessed - Process Crashes- Programmatically Trap The Condition

Apr 19, 2007

Workbook A has VBA code. The VBA code opens a collection of 2000 workbooks. The workbooks are processed one at a time, with Workbook A opening Workbook B, do something, close Workbook B, get next workbook. Sometimes Workbook B has some kind of access issue that results in:

"[File Name] cannot be accessed. The file may
be read-only, or you may be trying to
access a read-only location. Or, the
server the document is stored on may
not be responding.

If RETRY is clicked, the process returns the same message. If CANCEL is clicked, the process is crashed, Excel is closed, and all data processed is lost. What is the VBA syntax to programmatically trap this condition so it can be processed in an orderly manner?

View 2 Replies


Macro To Capture Who Accessed The File

Aug 25, 2008

Anyone know of a way to capture who (user name, machine name etc.) had the excel file open when a certain cell is selected? a Macro?

Trying to use an Excel spreadsheet for a peer review where each reviewer accesses the Excel peer reivew file and enters the minutes they spent in the peer review but would like to also capture the user that had the file open when the minutes spent on the peer review were entered. This would provide proof that the actual peer reviewer opened the file and entered their minutes.

View 9 Replies View Related

Finding Atribute Last Accessed Date Of A File And Make It A Range Value

Dec 21, 2009

I want to place the atribute (Date as value): "LastAccessDate" of a file called "!FileSessionStart.nwd" in Sheet1.Cell A2 in my workbook - without opening/accessing" this "C:/so-and-so/!FileSessionStart.nwd" - just want to read and use the last accessed date of it.

How will a macro go finding this atribute of this particular file and place this atribute as value as described?

The filepath of it is valued in celle A1

View 9 Replies View Related

Error Trap: Macro Opening Non Existing File

Oct 26, 2006

I have a user form with a TextBox (TextBox1), the user enters a filename and presses CommandButton1, this then opens the file. I have set an error trap so that if the file does not exist a MsgBox prompts the user to re-enter. So far so good, however if the error trap operates any further files entered are declared as non-existent even if I know they exist. I thought this would be quite easy but once again VBA bites me back!!

here is my code

Sub CommandButton1_Click()

stPath = ThisWorkbook.Path
stFilename = "Retention" & "" & TextBox1 & ".xls"
stFullname = stPath & "" & stFilename 'completes full path of for TextBox1

On Error Goto Errorhandler: 'go to this if file does not exist
Workbooks.Open stFullname
ActiveWorkbook.RunAutoMacros xlAutoOpen

View 6 Replies View Related

File Crashes When Opened

Jul 14, 2006

I run a macro which runs saves the active file to a different location to ensure that I retain the original like so -

If ActiveWorkbook. Name = "KEY_DATA_CFT77" + ".xls" Then
ActiveWorkbook.SaveAs Filename:= _
"H:HOME imcEXCELKEY_DATA_2006KEY_DATA_2006_01KEY_DATA_CFT77_01.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If .................................

View 9 Replies View Related

Crashes When Linking To Other Sheet Or File

Jan 13, 2010

my excel has developed the nasty habit of crashing when i try to link to another sheet or file.

i tried to copy the sheets to another file, and that worked.... for a while. i just cannot think to see what i do that causes this....

i am running excel 2003 (cheapskate company) and windows xp

View 6 Replies View Related

2007 Crashes When Saving A Specific File

Jul 1, 2009

Hi all, I've reinstalled office 2007 because excel 2007 was crashing very often when inserting new rows or when saving the file. I tried with other files and apparently work fine. The specific file can be opened but I don't know if is corrupted, I tried repairing it with open and repair from the file open menu but didn't work (it is still happening the same). I've been working on the file during few days and wouldn't like to start from scrath again, can anyone help me with this?

View 8 Replies View Related

Get Open File Crashes Upon Cancel Being Clicked

Mar 21, 2014

I have a userform where the user clicks a command button to select a gif/jpeg using the getopenfile function, the file path a is then loaded in to a text box and loaded in an image. I noticed that when the user cancelled before selecting a file it caused a crash. I have added some code to prevent this but now it crashed when an image is selected.

[Code] .....

View 3 Replies View Related

Crashes On UserForm Show By Next Button (Path/File Error)

Aug 7, 2008

When loading my workbook, my userform loads perfect, but when I go to hit the "next" button. It crashes with the error:

"Path/File access error."

The next button code, quite simple: ....

View 9 Replies View Related

Trap Cut & Copy Use

Dec 25, 2006

I have assigned some value to the id of the some cell.
now i have cut the cell and pastes into some other cell.
now the situation is id value is same for both the cell(cut and pasted).
As soon as i cut i would like to assign some new id value programtically to the cell which is being cut.

View 4 Replies View Related

Paste Special Command Cannot Be Accessed After A Selection Has Been Cut

Mar 2, 2009

My question is why the paste special command cannot be accessed after a selection has been cut? (same for Excel 2003 and Excel 2007).

View 3 Replies View Related

Excel 2007 :: Add Attachment To Spreadsheet That Can Be Accessed By Others?

Jul 23, 2013

I'm using Excel 2007, and when I add attachements to a spreadsheet cell (e.g. a saved email) nobody else can open the file when viewing the spreadsheet. Is there a way around this?

Currently I'm inserting it by going Object > Create from File > Browse > Select File > Display as Icon. But if the spreadsheet is emailed to anyone else or put on a staff website nobody can open the attachements.

View 8 Replies View Related

Constraining Number Of Days A Workbook Can Be Accessed

Jan 24, 2013

Is there any way to have a input box pop up after a Prescribed number of days or on a particular date and from that point on prompting the user to enter a password that I created (hard coded into the code)In order for the workbook to open If the password is not entered or is not correct then the workbook will close.After the pasword is enterd then the workbook would open from that point on(the input box would be disabled) Also this macro would have to be password protected in order to prevent the user from accessing the code.

View 8 Replies View Related

Trap Escape Key In Comment Box?

Mar 23, 2012

How would I trap the escape key being pushed while in a comment box?

In case user accidentally modifies comments box and they do not wish to save the changes. Pushing escape will save changes, which I do not wish to do.

This thread is closest code I found, but I do not know how to adapt it for the comment box.

Errror trapping with Application.Dialogs(xlDialogOpen).Show

View 2 Replies View Related

Trap Worksheet Scrolling

Sep 17, 2008

When the user scrolls the worksheet (using a scroll wheel, or the scroll bars) I would like to have a VBA-code triggered, but in Excel 2003 there is no "VBA-event" for scrolling...

Is there any way of trapping that a scroll has happened, even if the selection didn't change?

View 9 Replies View Related

Error Trap Always Executing

May 22, 2009

The error trap does work when there is an error, but it also runs at the end of the code when there isn't an error... I'm not sure what I've done wrong. When I test with data that is correct, it still runs through the code when it gets to the bottom. Have I put the error trap in the wrong place?

I have put in bold the section of the code with the error trap.

Option Explicit

Const TargetDB = "Group Life Pricing Tables v0.1.accdb"

Sub HMUK_Val()
Application.ScreenUpdating = False

'define variables to be used in the process
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String
Dim SchemeInfo As Integer
Dim CurrentAge As Integer
Dim CurrentRow As Integer
Dim CurrentSex As String
Dim Message
'check that Scheme data has already been entered
For SchemeInfo = 1 To 13
If Sheets("Working Info").Cells(SchemeInfo, 2).Value = "" Then
Message = MsgBox("Please ensure all Scheme information has been entered before processing member info", vbOKOnly, "Warning")
Exit Sub
End If
Next SchemeInfo

View 9 Replies View Related

Catch/Trap Formula Errors

Sep 15, 2006

I am trying to find some solution on frequent errors , that come up when one copy, or drag formulae.
Sometime the error doesnít appear on surface, but after digging you realize that some formula is not pointing to correct

I want to create a macro which will do following things :

1. It will go to each cell in the selected range, and compare the cellís formula with that of the cell on its right and left.
2. If the formulae are not similar it should change the color of that cell.

Basically, I am trying to catch logical errors. However I donít know how to approach this problem? Is there any in-built functionality in Excel that can do this task ?

View 9 Replies View Related

Trap Mouse And Keyboard Events Within VB Loop Only?

May 23, 2013

Is there a simple way to trap mouse and keyboard events in Excel, only within a VB loop, and then return to normal behavior after exiting the loop? That is, I want to know if a key is pressed (and what key) or the mouse clicked and not have it affect the spreadsheet. I don't want the key pressed to type into the spreadsheet.

View 1 Replies View Related

Trap Can't Find Project Or Library Error

Jul 6, 2007

I am using the calendar control on a form of mine, which i believe requires ms access to be installed to use(from what ive read). This is fine as most of the PC's it will be run on have this installed. However if it is run on a PC that doesnt have ms access it gives me the "Could not load an object because it is not avaliable on this machine" and then "cant find project or library error". How can I trap this error ? Ive tries whe the workbook opens, and when the form initializes but it just brings up that error

View 2 Replies View Related

Worksheet Open Event: Macro Run Every Time A Sheet In A Workbook Is Accessed

Jan 5, 2010

Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.

View 2 Replies View Related

Excel 2010 :: Workbook Not Closing Correctly When Accessed Via Link On Sharepoint Site

Feb 18, 2013

I was asked to create a userform for the use of our whole team which would enable them to select a name from a dropdown list and then it would display their business contact details in the rest of the form.

I'm running Office 2010 and Windows XP server/enterprise edition (not quite sure which). After some research I've managed to use VBA code to open the userform while hiding the workbook, then when the close button is clicked it closes the user form and it's associated workbook using the code below.

' ThisWorkbook module
Private Sub Workbook_Open()
Application.Visible = False
End Sub
'UserForm module
Private Sub CommandButton1_Click()
Unload UserForm1
ThisWorkbook.Close (False)
End Sub

This works great when the excel file is accessed directly. However when the excel file is accessed via the link on my teams Sharepoint site it doesn't. When the Userform is closed the underlying workbook and data is then displayed which then has to be closed separately.

View 2 Replies View Related

TreeView: Trap Click On Image Or Label Of Node

Dec 17, 2006

I am using a TreeView control and need to determine if the user clicked on the Image or the Label of a node. The Hittest function can return either a TreeViewHitTestInfo object or a node. By default it returns the node. I'm not quite sure how to trigger the return of a TreeViewHittestInfo object, but at the moment I'm even struggling to make VBA aware of that class. A simple statement like

Dim info As TreeViewHitTestInfo

returns an error saying that TreeViewHitTestInfo is not defined.

View 3 Replies View Related

Open A File If Condition Fulfilled

Mar 7, 2012

I want to open a file (Sam) if a a condition is fulfilled.

So, my file Sam.xls has a sheet named "Control" (veryhidden), the cell A1 has value (A1=5)

When I open Sam file, the code checks another file DOT.xls (the user should not see this process) and if it finds the same value in Sheet2 (veryhidden), cell A1=5, the file Sam to be opened.

If the code does not match the values the Sam file to be closed, Sheet "CHIO" to be deleted and the application closed.

View 5 Replies View Related

Multiple Condition Sum Across PivotTable & Another File Range

Oct 10, 2007

I need to find a value in a pivot table with a range of values over 12 months (Book5) when 3 criteria are met, Branch#, LOB# and Month#. Sum(if() works for the 1st month, but I do not know how to do the coding when the month changes.

Attached are copies of the two files I am working with and my coding so far....

View 5 Replies View Related

Userform Open A File And Then Figure Out If A Integer Meets A Condition

Oct 21, 2008

I have a userform that should open a file and then figure out if a integer meets a condition. All this works.

My problem is that i want to run another sub from this userform after the file is open(this will be the active file).

View 9 Replies View Related

List The File Name In One Excel Sheet, If Condition Is Met In 1000 Closed Workbooks

Nov 19, 2008

I have 1000 workbooks in a folder, each workbooks format is same.
as of now I open each workbook, and check if values in A10 is equal to B10, that is if the value in A10 is "100" I will check if B10 is also "100" if not I will make a note the file name.

I have more than 1000 workbooks in a folder, It is really difficult to open and check if the values are same. is there a macro, which will check the condition A10 = B10, if not get the file name in one sheet.

View 9 Replies View Related

Programmatically UpdateNamed Range

Jan 8, 2009

Programmatically UpdateNamed Range. Is it possibly to update a named range via VBA?

View 2 Replies View Related

Programmatically Add Listbox On Every Worksheet

Sep 15, 2012

Macro which will add a Listbox (Form control - not ActiveX) on each worksheet and give a sequential name to each ListBox.

View 5 Replies View Related

Programmatically Change Protected Add-in Using VBE?

Feb 10, 2013

The coding of the actual changes in the different modules is performing fine ...

But, I am stuck .. with two instructions : how to remove Add In password, and, how to set its IsAddIn property to false .

View 2 Replies View Related

Navigating To A Web Page Programmatically

Feb 1, 2008

Until recently I successfully used this code to navigate to a web page and populate some of it's boxes, before programatically pressing the page's "Validate" button, whereupon the page would test the data I'd populated the boxes with, and come back with an answer to tell me if there were any errors in my data:

Private Sub CommandButton1_Click()

Dim uf1_cbutt1_click_ie As Object

Set uf1_cbutt1_click_ie = CreateObject("InternetExplorer.Application")

Can anyone shed any light on how I may be able to programatically navigate with VBA to the page I want? I'm also having trouble finding the new name of the "Validate" button - and how to programatically click it.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved