Run-time error 75 Path/File access error
 

Hi,
I have a routine that copies a worksheet, opens the SaveAs dialog wit
a File name made up of values from named ranges and a date from th
copied sheet. It also attempts to make a new folder appended to th
current workbook path. Here's where I'm having trouble. I keep gettin
the run-time error 75. The problem is that the error is intermittent.
have tried moving the lines around thinking somehow the MkDir wa
picking up the newly copied sheet's path instead of the origina
workbook path, but no joy.

Here is the code:

Option Explicit

Private Sub cmdCopySaveAs_Click()
Dim c As Range
Dim d As Range
Dim NewSht As Worksheet
Dim rngDI As Date
Dim Fname As Variant
Dim str1 As Variant
Dim str2 As Variant
Dim str3 As Variant


str1 = Sheets("Proposal").Range("Lang1").Value
str2 = Sheets("Proposal").Range("Lang2").Value
str3 = Sheets("Proposal").Range("Lang3").Value
rngDI = Sheets("Proposal").Range("PropDate").Value
Fname = Sheets("Set-Up").Range("Project_Name").Value _
& " " & Sheets("Set-Up").Range("Contractor_s_Name").Value _
& Format(rngDI, " mm-dd-yyyy ")
MkDir ThisWorkbook.Path & "\Proposals" <<<<ERROR
On Error GoTo 0
Sheets("Proposal").Copy

Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "\Proposals\" & Fname & ".xls"
Set NewSht = ActiveSheet
On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet
.Shapes("cmdCopySaveAs").Delete
.Shapes("cmdPickScopes").Delete
.Range("Lang1") = str1
.Range("Lang2") = str2
.Range("Lang3") = str3
Set d
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
.Value = .Value
End With
Next c
End With
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Su

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=54001

Run-time error 75 Path/File access error
 

Try

If Dir(ThisWorkbook.Path & "\Proposals", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\Proposals"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Casey"
wrote in message
...

Hi,
I have a routine that copies a worksheet, opens the SaveAs
dialog with
a File name made up of values from named ranges and a date from
the
copied sheet. It also attempts to make a new folder appended to
the
current workbook path. Here's where I'm having trouble. I keep
getting
the run-time error 75. The problem is that the error is
intermittent. I
have tried moving the lines around thinking somehow the MkDir
was
picking up the newly copied sheet's path instead of the
original
workbook path, but no joy.

Here is the code:

Option Explicit

Private Sub cmdCopySaveAs_Click()
Dim c As Range
Dim d As Range
Dim NewSht As Worksheet
Dim rngDI As Date
Dim Fname As Variant
Dim str1 As Variant
Dim str2 As Variant
Dim str3 As Variant


str1 = Sheets("Proposal").Range("Lang1").Value
str2 = Sheets("Proposal").Range("Lang2").Value
str3 = Sheets("Proposal").Range("Lang3").Value
rngDI = Sheets("Proposal").Range("PropDate").Value
Fname = Sheets("Set-Up").Range("Project_Name").Value _
& " " & Sheets("Set-Up").Range("Contractor_s_Name").Value _
& Format(rngDI, " mm-dd-yyyy ")
MkDir ThisWorkbook.Path & "\Proposals" <<<<ERROR
On Error GoTo 0
Sheets("Proposal").Copy

Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "\Proposals\" & Fname & ".xls"
Set NewSht = ActiveSheet
On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet
Shapes("cmdCopySaveAs").Delete
Shapes("cmdPickScopes").Delete
Range("Lang1") = str1
Range("Lang2") = str2
Range("Lang3") = str3
Set d =
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
Value = .Value
End With
Next c
End With
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread:
http://www.excelforum.com/showthread...hreadid=540012

Run-time error 75 Path/File access error
 

Chip,
Thank you very much. That seemed to cure the problem. Sorry it took me
a while to respond, I had a meeting first thing this morning that
turned into a marathon. Your help is much appreciated.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=540012