About Us

Debbie[_6_]

"Tom Ogilvy" wrote in message ...
I couldn't reproduce the behavior you describe:

ActiveWorkbook.SaveAs "C:\Data\AAAAA'S.XLS"
? activeWorkbook.Name
AAAAA'S.XLS
Activeworkbook.SaveAs "AAAAA#.xls"
? activeWorkbook.Name
AAAAA#.xls

Xl97 SR2, Windows 98 SE

--
Regards,
Tom Ogilvy

Debbie wrote in message
om...
(Debbie) wrote in message
om...
Hello -

I have code that opens every file in a specified
folder, one by one, and retrieves the month and
the customer name from certain cells on the
active worksheet. It then uses the customer
name as criteria to determine if a folder for
that customer already exists, and if not, it
creates the folder/path for that customer.
The workbook then closes, and all files with
the same customer name are moved into that
customer's folder. This goes on until all
files in the specified folder have been
moved to their own respective folders.

All works exactly as intended until Excel
encounters a file with an apostrophe or a
pound sign (#) in the file name. The folder
for the customer is created without incident,
but when the workbook closes and Excel looks
for the files containing the customer name,
I get a "File Cannot Be Found" error. I've
finally figured out that it cannot find the
file because Excel drops the ".xls" extension,
and appends a numerical value to the file name.

I developed a "fix" for this, but since I do
not know what to do to prevent it (other than
the obvious - not to use apostrophes and pound
signs in the file name), I don't know what
kind of error-checking to do. For instance,
my "fix" involves looking for numbers at the
end of any file name that does not have an
".xls" extension, removing those numbers, and
appending the extension. However, I will have
files that end with numbers, so I can see a
potential problem if the customer name contains
one of the troublesome characters. And since
Excel appends the numbers according to the
order in which the file was opened (I think),
I cannot determine what the appended number
will be. I tried using code to count the
files (".FoundFiles.Count") in the specified
folder to get a maximum number that could be
appended, but I could not get the code to
work.

If you see a way to change the following code
to accommodate all files, or a way to prevent
Excel from changing the file name, I sure can
use the help.

Thanks,

Debbie

Sub MoveFilesToFinalLocation()
Dim FirstWorkbookOpened As Workbook
Dim SubsequentWorkbooksOpened As Workbook
Dim SourcePath As String
Dim DestinationPath As String
Dim JustTheFile As String
Dim i As Long
Dim j As Long
Dim NumberOfNumbers As Long
Dim CharactersToSubtract As Long

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\Insertion Orders"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set FirstWorkbookOpened = Workbooks.Open(.FoundFiles(1))

FirstWorkbookOpened.Activate
ActiveWorkbook.Sheets("OrderEntry").Activate

' This is where the info is pulled from the
' active sheet, and the check is performed
' as to whether or not a folder needs to be
' created for the customer.
Call CheckForOrCreateCustomerFolder

JustTheFile = FirstWorkbookOpened.Name

If Right(JustTheFile, 4) < ".xls" Then

For j = 1 To Len(JustTheFile)
NumberOfNumbers = 0
If IsNumeric(Mid(JustTheFile, j, 1)) Then
NumberOfNumbers = NumberOfNumbers + 1
End If
Next j

If NumberOfNumbers 0 Then
CharactersToSubtract = NumberOfNumbers
JustTheFile = Left(JustTheFile, Len(JustTheFile) - _
CharactersToSubtract)
End If

JustTheFile = JustTheFile & ".xls"

'MsgBox JustTheFile
End If

FirstWorkbookOpened.Close SaveChanges:=False

SourcePath = "C:\Insertion Orders\"
DestinationPath = PathName

Name SourcePath & JustTheFile As DestinationPath _
& JustTheFile

For i = 2 To .FoundFiles.Count
Set SubsequentWorkbooksOpened =
Workbooks.Open(.FoundFiles(i))

SubsequentWorkbooksOpened.Activate
ActiveWorkbook.Sheets("OrderEntry").Activate
ActiveSheet.Range("A1").Select

Call CheckForOrCreateCustomerFolder

JustTheFile = SubsequentWorkbooksOpened.Name

If Right(JustTheFile, 4) < ".xls" Then

For j = 1 To Len(JustTheFile)
NumberOfNumbers = 0
If IsNumeric(Mid(JustTheFile, j, 1)) Then
NumberOfNumbers = NumberOfNumbers + 1
End If
Next j

If NumberOfNumbers 0 Then
CharactersToSubtract = NumberOfNumbers
JustTheFile = Left(JustTheFile,
Len(JustTheFile) - _
CharactersToSubtract)
End If

JustTheFile = JustTheFile & ".xls"

'MsgBox JustTheFile
End If

SubsequentWorkbooksOpened.Close SaveChanges:=False

SourcePath = "C:\Insertion Orders\"
DestinationPath = PathName

Name SourcePath & JustTheFile As DestinationPath & _
JustTheFile

Next i
Else
Exit Sub
End If
End With

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

P.S. - I tried telling Excel to replace the
apostrophe with double apostrophes (''), and
to replace the pound sign with some other
character temporarily, but that made no
difference.

~~~~~~~~~~~~~~~~~~~

Hey Again,

Well, I've got some additional info, but I had to
wait until I saw my post so as not to start a new
thread. Since my original post, I've discovered that
Excel does not change the file names of files with
apsotrophes and pound signs for files that I save
(or Save As) manually. I can open a file with a
file name that was created/saved via code, save it
as the exact same name (which Excel recognizes as
the exact same name), and Excel does not drop the
extension and append numerals when that file is
being moved during the "Move" macro. What gives?

Does this new discovery shed any more light on
what may be happening?

Also, I changed the portion of code that checks
for a missing extension so that it is a little
more correct, although maybe not perfect, as
shown below (not that it has any bearing on the
original problem).

Thanks,

Debbie

If Right(JustTheFile, 4) < ".xls" Then

NumberOfNumbers = 0
For j = 1 To Len(JustTheFile)
If IsNumeric(Right(JustTheFile, j)) Then
NumberOfNumbers = NumberOfNumbers + 1
End If
Next j

If NumberOfNumbers 0 Then
CharactersToSubtract = NumberOfNumbers
JustTheFile = Left(JustTheFile, Len(JustTheFile) - _
CharactersToSubtract)
End If

JustTheFile = JustTheFile & ".xls"
End If

Hi Guys,

Sorry. How ignorant of me not to mention that I use xl97 and xl2000
on Win98, and I get the same results in both. I also failed to
mention that the trouble is when the pound signs and apostrophes are
within the file name, and not at the end, such as:

Mr. Toad's Wild Ride - October Taxi Special - DY.xls
Breathe Freely Respiratory Service - #325 - TB.xls

Jase, I have been trying your suggestion, but I cannot come up with
the correct code to make it work consistently. So, what I am now
working on is reading the "Found" file name and inserting that name
into a cell in a temp workbook, and then opening up that file and
offsetting the required info into adjacent cells, checking for and/or
creating the needed folder(s), then closing the workbook. Then it
reads the next unopened "Found" file and inserts it into the next row
of the temp workbook, then on and on. Then, after all the file names
and associated info are in the temp workbook, the idea is to move the
files to their new locations according to the info in the temp
workbook. However, Excel keeps crashing when it is inserting the
information from the last workbook into the temp workbook.

Can either of you, by any chance, give me a step up on the correct
code to do what Jase suggested? Or maybe you can provide a suggestion
to keep Excel from crashing (Excel.exe caused invalid page fault in
module unknown) when retrieving info from the last file in the folder.

Your responses are much appreciated, as will be any further
suggestions.

Thank You,

Debbie

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Unwanted code execution	Rick	Excel Discussion (Misc queries)	2	March 26th 10 02:14 PM
Help - VBA Code execution in Excel	Luis[_2_]	New Users to Excel	2	September 10th 07 03:14 AM
Excel VBA Code Execution Excel XP Vs Excel Office 2000	John Flynn	Excel Programming	6	August 24th 03 04:58 AM

Menu

About Us