Webtest
Dec 12th, 2005, 09:55 AM
Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 VBA
Several people submit workbooks to me, and I take the data from these books and use a macro to generate reports from them. One individual submitted the standard 2 workbooks, but for some reason, VBA generates an error when attempting to open the files - it executes the file not found abort:'SNIPPET ONLY!!!
Option Explicit
'GLOBAL Path for Department Files
Public gs_DeptPath As String
'GLOBAL Filename for STORES file
Public gs_DeptStoresFilename As String
Sub Macro1()
Dim s_stores As String
Dim s_regions As String
'Initialize the filespec
gs_DeptPath = "C:\TEST\"
gs_DeptStoresFilename = "RawFruit_By_Store"
'Hey, lets see if the files exist!
s_stores = Dir(gs_DeptPath & gs_DeptStoresFilename & ".xls")
If Len(s_stores) = 0 Then GoTo G_ABORT_NOSTOREFILE
'Open the STORES file if it is not already open
i = 0
On Error Resume Next 'Ignore the error for "Not in Workbooks Collection"
'If the STORES File is open, it is in the WorkBooks Collection
i = Len(Workbooks(gs_DeptStoresFilename & ".xls").Name)
If i = 0 Then
' Open the Stores file read-only
On Error GoTo G_ABORT_OPENSTORES 'The file exists, but is not open and can't be opened.
Workbooks.Open Filename:=gs_DeptPath & gs_DeptStoresFilename & ".xls", ReadOnly:=True '<< ERROR IS HERE
End If
On Error GoTo 0 'Reset the Error Handler
Exit Sub
'Error Handlers go HERE:
G_ABORT_NOSTOREFILE:
G_ABORT_OPENSTORES: 'This ABORT gets executed
End SubAll users are running Office/Excel 2003, but only this one user has a problem with the .xls files not being openable by VBA. The files open just fine without any kind of notification in Excel. I resolved the problem temporarily by renaming the original files, opening the files on my own desktop, and doing a Save As to the correct expected file name. After that, the VBA macro runs perfectly.
An ideas as to what could be preventing VBA from opening the files? Thank you for any and all comments, suggestions, and assistance.
===============================
Excel 2003 VBA
Several people submit workbooks to me, and I take the data from these books and use a macro to generate reports from them. One individual submitted the standard 2 workbooks, but for some reason, VBA generates an error when attempting to open the files - it executes the file not found abort:'SNIPPET ONLY!!!
Option Explicit
'GLOBAL Path for Department Files
Public gs_DeptPath As String
'GLOBAL Filename for STORES file
Public gs_DeptStoresFilename As String
Sub Macro1()
Dim s_stores As String
Dim s_regions As String
'Initialize the filespec
gs_DeptPath = "C:\TEST\"
gs_DeptStoresFilename = "RawFruit_By_Store"
'Hey, lets see if the files exist!
s_stores = Dir(gs_DeptPath & gs_DeptStoresFilename & ".xls")
If Len(s_stores) = 0 Then GoTo G_ABORT_NOSTOREFILE
'Open the STORES file if it is not already open
i = 0
On Error Resume Next 'Ignore the error for "Not in Workbooks Collection"
'If the STORES File is open, it is in the WorkBooks Collection
i = Len(Workbooks(gs_DeptStoresFilename & ".xls").Name)
If i = 0 Then
' Open the Stores file read-only
On Error GoTo G_ABORT_OPENSTORES 'The file exists, but is not open and can't be opened.
Workbooks.Open Filename:=gs_DeptPath & gs_DeptStoresFilename & ".xls", ReadOnly:=True '<< ERROR IS HERE
End If
On Error GoTo 0 'Reset the Error Handler
Exit Sub
'Error Handlers go HERE:
G_ABORT_NOSTOREFILE:
G_ABORT_OPENSTORES: 'This ABORT gets executed
End SubAll users are running Office/Excel 2003, but only this one user has a problem with the .xls files not being openable by VBA. The files open just fine without any kind of notification in Excel. I resolved the problem temporarily by renaming the original files, opening the files on my own desktop, and doing a Save As to the correct expected file name. After that, the VBA macro runs perfectly.
An ideas as to what could be preventing VBA from opening the files? Thank you for any and all comments, suggestions, and assistance.