Function Call to check Workbooks.Open

[RompStar]

HI there... I written a Sub that opens Excel files from a network
drive and imports them into an Excel sheet one at a time in an append
fashion inside a loop. So, I have create a different Sub previously
that export Excel files to their respective network folders, and then
employees work on it, and then we needed an automatic way to import
them all in for analysis. I have a Log from from the previous export
process so records the locations/file names of all the excel files
that were exported, so I use that to import them back in.

My Sub works very well, but I wanted to include a way to check when
the File is being opened for different supprise conditions that can
arise, including errors.

I am not sure how Excel behaves in opening a file if someone else has
it open and if working on it.

or

IF the file is missing for some reason from the expected location
where it should be

or whatever other conditions there exists that could become
problematic, basically I want the script to cycle through all the
files and handle errors or problems gracefully so that the VBA don't
error out.

Anyways, I am posting my entire VBA script that I did, which works,
but I wanted to add some handling, and I am not sure if a Function
Call would be appropriate here, have little experience with Function
Calls. Also, I consider my skills like beginner to maybe a little
intermediate, so if anyone has suggestions on making the script
better, let me know.

But basically it reads the locations and file names from a Log sheet,
so addresses would be something like this:

Location the Store folders were written to:
c:\Documents and Settings\LANID\Desktop\test_store_import\Store_1_.xls
c:\Documents and Settings\LANID\Desktop\test_store_import\Store_2_.xls

Location the Store folders were written to, but in the real script
they will point to network drives, instead of my local computer for
testing..... The script also strips the store number value from the
file name, so that the right sheet for copy can be selected, which in
this example would be a sheet named 1, which represents store number
1, thanks a lot!

code below ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++

Sub Import_From_Log()

Dim wsLogName, wsOpenName As String
Dim wsNew, wsLog As Worksheet ' New sheet that will contain all the
imported Excel sheets, appended in one sheet
Dim wsLogRange As Range
Dim FileToOpen, get_store_number As String
Dim lastrow, lastrow2, lastrow3, real As Long
Dim RangeCell As Range
Dim Switch As Worksheet

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

Application.ScreenUpdating = False

With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row ' last row is
stored in variable RangeFinder
Set wsLogRange = Range("A2:A" & lastrow)
End With

Set wsNew = Sheets.Add ' Add the new workbook that will contain the
updated lists
wsNew.Name = "File_Import_Appended"

For Each Value2 In wsLogRange

FileToOpen = Value2

With wsLog
Workbooks.Open FileName:=FileToOpen ' works up to here
wsOpenName = ActiveWorkbook.Name ' record the name of Excel
file just opened into a variable for later usage...
get_store_number = Left(Mid(wsOpenName, 7), 1) ' gets the value
for the sheet number to select and copy from, ignore all else

ActiveWorkbook.Sheets(get_store_number).Activate ' make sure to
only select the store sheets to copy from

Range("A1").Select ' start by placing cursor into cell A1
Selection.End(xlToRight).Select ' find last column
Selection.End(xlDown).Select ' next - find last row
Set RangeCell = ActiveCell ' record the right
side of the range and assign to RangeCell
Range("A1:" & RangeCell.Address()).Select ' select
the range
Selection.Copy ' now
copy it
Windows(wsLogName).Activate ' switch
back to the Append sheet
ActiveWorkbook.Sheets("File_Import_Appended").Activate ' make
sure we are activating the append sheet

With wsNew
lastrow2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With

If lastrow2 = 1 Then ' if sheet is blank that mean nothing was
pasted, so start here for the first paste
ActiveSheet.Paste
Else
real = lastrow2 + 1 ' if the sheet was already posted, we want
all other pastes to follow this rule here
Range("A" & real).Select
ActiveSheet.Paste
End If

Application.Windows(wsOpenName).Activate
Application.CutCopyMode = False ' clear the clipboard, otherwise a
window will popup asking if clipboard contents should be saved
ActiveWorkbook.Close SaveChanges:=False ' close the Import
Excel Workbook
End With

Next Value2

With wsNew
Cells.EntireColumn.AutoFit
End With


Application.ScreenUpdating = True

End Sub

 

[Jim Cone]

This might be a little better...
'--
Sub Import_From_Log()
Dim wsLogName As String
Dim wsOpenName As String
Dim wsNew As Worksheet
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen As String
Dim get_store_number As String
Dim lastrow, lastrow2, lastrow3, real As Long
Dim RangeCell As Range
Dim Switch As Worksheet

Dim Value2 As Range

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet
Application.ScreenUpdating = False

With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set wsLogRange = Range("A2:A" & lastrow)
End With

Set wsNew = Sheets.Add
wsNew.Name = "File_Import_Appended"

For Each Value2 In wsLogRange.Cells
FileToOpen = Value2
On Error Resume Next
Workbooks.Open Filename:=FileToOpen
If Err.Number = 0 Then
On Error GoTo 0
wsOpenName = ActiveWorkbook.Name
get_store_number = Left(Mid(wsOpenName, 7), 1)
ActiveWorkbook.Sheets(get_store_number).Activate
Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Set RangeCell = ActiveCell
Range("A1:" & RangeCell.Address()).Select
Selection.Copy
Windows(wsLogName).Activate
ActiveWorkbook.Sheets("File_Import_Appended").Activate
With wsNew
lastrow2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With
If lastrow2 = 1 Then ' if sheet is blank that mean nothing was
'pasted, so start here for the first paste
ActiveSheet.Paste
Else
real = lastrow2 + 1 ' if the sheet was already posted, we want
'all other pastes to follow this rule here
Range("A" & real).Select
ActiveSheet.Paste
End If
Application.Windows(wsOpenName).Activate
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
Else
On Error GoTo 0
MsgBox "Could not open " & FileToOpen & " ", vbExclamation, "ALERT"
End If
Next 'Value2

wsNew.Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"RompStar" <[email protected]>
wrote in message
-snip-
I am not sure how Excel behaves in opening a file if someone else has
it open and if working on it.
or
IF the file is missing for some reason from the expected location
where it should be
or whatever other conditions there exists that could become
problematic, basically I want the script to cycle through all the
files and handle errors or problems gracefully so that the VBA don't
error out.
-snip-

 

[RompStar]

Great, thanks! for the reply... learned more...

Had one more minor question...

get_store_number = Left(Mid(wsOpenName, 7), 1)

This line of code [above], what that does is, it looks at the name of
the activeworkbook that is being opened... the Names has a strick
naming convension, like:

Store_x_restof_file_name.xls

Where x is the store number, I want to extract the store number and
then select the sheet from this workbook, which will have a store
number, that's the sheet that I want to copy from. Currently this
works, but it is extracting the first digit only from the _x_, what if
it is a 2 digit number like: Store_22_ or 3 digits Store_900_ How can
I accurately extract it ? I don't know everything yet, far from that,
learning daily, thanks!

If anyeone could assist me, thanks!

 

[Jim Cone]

Sub FindStoreNumTwoWays()
Dim s As String
Dim v As Variant

s = "Store_123_restof_file_name.xls"
'Returns 0 based string array
v = VBA.Split(s, "_", -1, vbTextCompare)
s = v(1)
MsgBox s & " ...first method"

'-or-

s = "Store_123_restof_file_name.xls"
'Val returns numbers from a string - numbers must come first
s = Val(Left(Mid(s, 7), 99))
MsgBox s & " ...second method"
End Sub
'--

P.S.
Value2 is a property of the range object.
Better to use another name for a variable.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"RompStar" <[email protected]>
wrote in message
Great, thanks! for the reply... learned more...
Had one more minor question...
get_store_number = Left(Mid(wsOpenName, 7), 1)

This line of code [above], what that does is, it looks at the name of
the activeworkbook that is being opened... the Names has a strick
naming convension, like:
Store_x_restof_file_name.xls

Where x is the store number, I want to extract the store number and
then select the sheet from this workbook, which will have a store
number, that's the sheet that I want to copy from. Currently this
works, but it is extracting the first digit only from the _x_, what if
it is a 2 digit number like: Store_22_ or 3 digits Store_900_ How can
I accurately extract it ? I don't know everything yet, far from that,
learning daily, thanks!
If anyeone could assist me, thanks!

 

[RompStar]

Thanks for the help, I guess I should have been more clearer :- )

The file name convension is like this:

Store_123_rest_of_file_name_20080101.xls

At the end of the file name there is a date that the document must be
destroyed by, just curious how I would deal with that... The code:

Val(Left(Mid(s, 7), 99)) works if I have numbers only in the
beginning, but if there is a mix towards the end, it returns 0 for the
store number
when I place a variable watch.

 

[RompStar]

Nevermind, I figured it out. Thanks for all the help and the time you
put, appreciated.

Cheers.