Strange VBA macro problem

[hovious3]

I have successfully created some VBA code to check for and create
folders, and then save a file to that folder.

However, when you pull up the excel sheet, complete your work, and then
run the macro, it will not run on the first attempt. A run time error
1004 pops up - cannot access file. What is strange is either hit end
or debug (then close out the editor), run the macro again, and it works
just fine. Furthermore, anytime you change the date and quote number
within the excel sheet to a new month/number, the macro will not run on
the first attempt. Hit end or debug, then it works just fine.

I have tried to reboot my PC, but this does not work. Not sure if it's
a Windows OS or maybe Excel application issue.

Can someone look at the following data and determine if it's something
wrong with the code syntax or an issue with the excel sheet? Pulling
my hair out!!

Any advice is greatly appreciated.


CODE:

Sub make_folder_test()
If Len(Dir(ActiveWorkbook.Worksheets(1).Range("f1").Value,
vbDirectory)) < 1 Then
MkDir ActiveWorkbook.Worksheets(1).Range("f1").Value
ElseIf Len(Dir(ActiveWorkbook.Worksheets(1).Range("f2").Value,
vbDirectory)) < 1 Then
MkDir ActiveWorkbook.Worksheets(1).Range("f2").Value
End If
FName = ActiveWorkbook.Worksheets(1).Range("A1").Value
FPath = ActiveWorkbook.Worksheets(1).Range("f2").Value
FSuffix = " Tool RFQ"
FSpec = FPath & FName & FSuffix
ActiveWorkbook.SaveAs Filename:=FSpec
End Sub


EXCEL SHEET USES INPUT DATA:
Date: 11/5/2005
DUE DATE: 11/10/2005
Customer Name: Fasco
Job Description: Trans Housing
Quote Number(s): 0511500
Cost Development: Tim


EXCEL CREATES FILENAME: "0511500 Fasco Trans Housing"


EXCEL SHEET CREATES FOLDER NAMES:
\Quotes 0511\
\Fasco\
using concatenate and splitting first four digits of quote number by
using LEFT,(cell,decimal)command.


EXCEL SHEET CREATES PATHS:
t:\costestimators\tooling\testing\Quotes 0511\
t:\costestimators\tooling\testing\Quotes 0511\Fasco\


Best regards,
Steve

 

[Bernie Deitrick]

Steve,

Your code worked fine for me on the first running. Perhaps your code module
is beginning to become corrupted: try copying the code into a module of a
new workbook.

HTH,
Bernie
MS Excel MVP

 

[hovious3]

Bernie,

Excellent idea, but I had no luck. I also tried to run two other
macros I have setup in the same code module, and they worked fine.

You know, one of the macros that functions properly is basically the
same as the one that is giving me trouble, except it has only one
check/confirm for a folder:

Sub make_folder_()
If Len(Dir(ActiveWorkbook.Worksheets(1).Range("f1").Value,
vbDirectory)) < 1 Then
MkDir ActiveWorkbook.Worksheets(1).Range("f1").Value
End If
FName = ActiveWorkbook.Worksheets(1).Range("A1").Value
FPath = ActiveWorkbook.Worksheets(1).Range("f1").Value
FSuffix = " Tool RFQ"
FSpec = FPath & FName & FSuffix
ActiveWorkbook.SaveAs Filename:=FSpec
End Sub

This code works without the difficulty that the other one is giving me.
Weird.

Thank you for the response!

Steve