Solved: Save as and File Directory

[fredlo2010]

Hello, I have been trying to create a code that will save my file in a specific location and with a specific name based on cell values.

Here is the code I have till now. But I cannot get it to work at all it takes me to the error handler part.

Option Explicit

Sub SaveDocumentAs()

Dim varNameFolder As String
Dim varProjectFolder As String
Dim varFile As String
Dim varPath As String
Dim varMsg As String

On Error GoTo Invalid_Character_cell

varNameFolder = Range("B2").Text
varProjectFolder = Range("B3").Text
varFile = Range("B4").Text

If varNameFolder = "" Or varProjectFolder = "" Or varFile = "" Then

varMsg = ("File was not saved. Please make sure: " _
& vbCr & Chr(149) & " Name, Folder and File have been entered")
MsgBox varMsg
End If


varPath = "C:\" & varNameFolder & "\" & varProjectFolder & "\"

'Check if the file exists already

If Not varPath <> "" Then

MsgBox "The file " & varFile & " already exists. Would you like to replace it?", _
vbYesNo + vbCritical, "Overwrite File"
If vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
Application.DisplayAlerts = False

MsgBox ("Saved" & varFile)
End If

End If

MkDir varPath

ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile



Invalid_Character_cell:

MsgBox ("Make sure Company Name, Jobsite and File have only letters and numbers")
End Sub

Thanks for the support.

[Tinbendr]

Not tested.

Sub SaveDocumentAs()

Dim varNameFolder As String
Dim varProjectFolder As String
Dim varFile As String
Dim varPath As String
Dim varMsg As String

On Error GoTo Invalid_Character_cell

varNameFolder = Range("B2").Text
varProjectFolder = Range("B3").Text
varFile = Range("B4").Text

If varNameFolder = "" Or varProjectFolder = "" Or varFile = "" Then

varMsg = ("File was not saved. Please make sure: " _
& vbCr & Chr(149) & " Name, Folder and File have been entered")
'MsgBox varMsg

varPath = "C:\" & varNameFolder & "\" & varProjectFolder & "\"

'Check if the file exists already
'This isn't checking for file existance.
'It's only checking if the string is empty.
'Which is really redundant since you already
'check for foldernames.
If varPath <> "" Then

'to check for file existing see
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=559
If FileOrDirExists(varPath) Then
MsgBox "The file " & varFile & " already exists. Would you like to replace it?", _
vbYesNo + vbCritical, "Overwrite File"

If vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
Application.DisplayAlerts = False
End If

Else

MkDir varPath
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
End If

MsgBox ("Saved" & varFile)
End If

MsgBox ("Make sure Company Name, Jobsite and File have only letters and numbers")
End If
End Sub

[fredlo2010]

Hi David,

Thanks you so much for your help. I tested the file but it does not do anything at all. It does not give me an error message as before or anything. This is the code I am using plus the Function to check if the file or directory exist

Option Explicit

Sub SaveDocumentAs()

Dim varNameFolder As String
Dim varProjectFolder As String
Dim varFile As String
Dim varPath As String
Dim varMsg As String



varNameFolder = Range("B2").Text
varProjectFolder = Range("B3").Text
varFile = Range("B4").Text

If varNameFolder = "" Or varProjectFolder = "" Or varFile = "" Then

varMsg = ("File was not saved. Please make sure: " _
& vbCr & Chr(149) & " Name, Folder and File have been entered")
'MsgBox varMsg

varPath = "C:\" & varNameFolder & "\" & varProjectFolder & "\"

'Check if the file exists already
'This isn't checking for file existance.
'It's only checking if the string is empty.
'Which is really redundant since you already
'check for foldernames.
If varPath <> "" Then

'to check for file existing see
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=559
If FileOrDirExists(varPath) Then
MsgBox "The file " & varFile & " already exists. Would you like to replace it?", _
vbYesNo + vbCritical, "Overwrite File"

If vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
Application.DisplayAlerts = False
End If

Else

MkDir varPath
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
End If

MsgBox ("Saved" & varFile)
End If

MsgBox ("Make sure Company Name, Jobsite and File have only letters and numbers")
End If
End Sub




Function FileOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file
' or folder exists, false if not.
'PathName : Supports Windows mapped drives or UNC
' : Supports Macintosh paths
'File usage : Provide full file path and extension
'Folder usage : Provide full folder path
' Accepts with/without trailing "\" (Windows)
' Accepts with/without trailing ":" (Macintosh)

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select

'Resume error checking
On Error GoTo 0
End Function

[GTO]

Hi Fred,

Minimally tested, but does this help?

In a Standard Module:
Option Explicit

Sub SaveDocumentAs()
Dim varNameFolder As String
Dim varProjectFolder As String
Dim varFile As String
Dim varPath As String

Dim n As Long

varNameFolder = Range("B2").Text
varProjectFolder = Range("B3").Text
varFile = Range("B4").Text

For n = 0 To 2
'// Check the two proposed folder names and the proposed filename //
If Not IsLegalName(Array(varNameFolder, varProjectFolder, varFile)(n), True) Then
MsgBox "one of the names is illegal, exiting", 0, vbNullString
Exit Sub
End If
Next

'// Ensure trailing seperators //
If Not FixPath(varNameFolder) _
Or Not FixPath(varProjectFolder) Then
MsgBox "something is wacky"
Exit Sub
End If

'// Not sure about this, but some check to see if user entered extension as part of //
'// proposed filename //
If Not InStrRev(varFile, ".") >= Len(varFile) - 4 Then
varFile = varFile & Mid(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, "."))
End If

varPath = "C:\"

'// Errr... With the above base path, we're safe, but for another base path, see //
'// if exists //
If Not FileOrDirExists(varPath) Then
MsgBox "Base path doesn't exist"
Exit Sub
End If

'// Create folders as necessary //
If Not FileOrDirExists(varPath & varNameFolder) Then
MkDir varPath & varNameFolder
End If
If Not FileOrDirExists(varPath & varNameFolder & varProjectFolder) Then
MkDir varPath & varNameFolder & varProjectFolder
End If

'// Then combine all for the file's FullName //
varPath = varPath & varNameFolder & varProjectFolder & varFile

If FileOrDirExists(varPath) Then

If MsgBox("The file " & varFile & " already exists. Would you like to replace it?", _
vbYesNo Or vbCritical, "Overwrite File") = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = False
End If
Else
ActiveWorkbook.SaveAs Filename:=varPath
End If
End Sub

Function IsLegalName(ByVal Path As String, Optional IsFileOrFolderName As Boolean = True) As Boolean
Dim sIllegalChars As String
Dim n As Long

Const MAXLEN As Long = 31
Const RB As String = "]"
Const HX As String = "History"

If Len(Path) = 0 Then Exit Function

If IsFileOrFolderName Then
sIllegalChars = "[\/:*?""<>|" ' Keep "]" seperate to use Like
Else
sIllegalChars = "[*?/\:"
End If

For n = 1 To Len(Path)
If Mid(Path, n, 1) Like "[" & sIllegalChars & "]" Or Mid(Path, n, 1) = RB Then
Exit Function
End If
Next

If Not IsFileOrFolderName And (Path = HX Or Len(Path) > MAXLEN) Then
Exit Function
End If

IsLegalName = True
End Function

Function FixPath(Path As String, Optional StripTrailing As Boolean = False) As Boolean
Do While Len(Path) > 0 And Right(Path, 1) = Application.PathSeparator
Path = Left(Path, Len(Path) - 1)
Loop
If Len(Path) > 0 And Not StripTrailing Then Path = Path & Application.PathSeparator
If Len(Path) >= 3 Then
FixPath = True
End If
End Function

'by: Ken Puls
'Example: http://www.vbaexpress.com/kb/getarticle.php?kb_id=559
Function FileOrDirExists(PathName As String) As Boolean

'Macro Purpose: Function returns TRUE if the specified file
' or folder exists, false if not.
'PathName : Supports Windows mapped drives or UNC
' : Supports Macintosh paths
'File usage : Provide full file path and extension
'Folder usage : Provide full folder path
' Accepts with/without trailing "\" (Windows)
' Accepts with/without trailing ":" (Macintosh)

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select

'Resume error checking
On Error GoTo 0
End Function

Mark

[Tinbendr]

As usual, Mark goes goes above and beyond!!!!!

Great code.

Here's my meager attempt.

Sub SaveDocumentAs()

Dim varNameFolder As String
Dim varProjectFolder As String
Dim varFile As String
Dim varPath As String
Dim varMsg As String
Dim Ans As Long

varNameFolder = Range("B2").Value
varProjectFolder = Range("B3").Value
varFile = Range("B4").Value

If varNameFolder <> "" Or varProjectFolder <> "" Or varFile <> "" Then

varMsg = ("File was not saved. Please make sure: " _
& vbCr & Chr(149) & " Name, Folder and File have been entered")
'MsgBox varMsg

varPath = "C:\" & varNameFolder & "\" & varProjectFolder & "\"

'Check if the file exists already
If varPath <> "" Then

'to check for file existing see
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=559
'Check that path/file exist
If FileOrDirExists(varPath & "\" & varFile) Then
Ans = MsgBox("The file " & varFile & " already exists. Would you like to replace it?", _
vbYesNo + vbCritical, "Overwrite File")

If Ans = 6 Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
Application.DisplayAlerts = True
MsgBox ("Saved" & varFile)
End If

Else
On Error GoTo FileErr
MkDir varPath
ActiveWorkbook.SaveAs Filename:=varPath & "\" & varFile
MsgBox ("Saved" & varFile)
On Error GoTo 0
End If


End If

MsgBox ("Make sure Company Name, Jobsite and File have only letters and numbers")
End If
Exit Sub
FileErr:
Select Case Err

'put other specific error handling here.

Case Else
MsgBox Err & " " & Err.Description

End Select
Err.Clear

End Sub

[fredlo2010]

Wow guys, thanks a lot for the help this works perfectly. I am using Mark's version. I only added a little more information to this part:


Dim as String
varIlligalChar = "[\/:*?""<>|"

If Not IsLegalName(Array(varNameFolder, varProjectFolder, varFile)(n), True) Then
MsgBox "File was not saved. Please make sure: " & vbCr _
& vbCr & Chr(149) & " Name, Folder and File have been entered" _
& vbCr & Chr(149) & " The names can not contain Special Characters: " & varIlligalChar, 0, "Empty cells"
Exit Sub
End If

Thanks you very much guys for the efforts. I would have never come up with something like that. I guess there is a long road of learning ahead of me.

Note:

Guys, I am new to this forums and I see the same people around answering questions most of the time. I am working on a big project and looking for new solutions and most of the time I face problems and I have to post questions here. How bad does it look when the same person posts too many threads?

Thanks again for the Help :)

[Tinbendr]

How bad does it look when the same person posts too many threads?As long as you are putting forth some effort, it shouldn't be a problem.

If you show up with your hand out, I'm gonna recommend a consultant.


I am working on a big projectIn this regard, kinda give a little background about the project in relation to the problem. We may know of a different solution that works better.

Good luck!

[fredlo2010]

Thanks for the reply. I will definitively try and research and try again before posting here.

I don't want a consultant, I want to learn this. As a matter of fact I might take some VB classes next semester and see how it goes.
I am reading Mastering VBA for Office 2010.

For sure I will always explain what I want maybe I am thinking one thing and the result might be easier than we thought. Sometimes we have things in front of our eyes and we don't see them.

Thanks for the input and help.

[jolivanes]

Mark / fredlo



Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = False


I assume that this is a slip of the keyboard fingers.

Tinbendr has it right with True in the 2nd statement

Not being picky but I assume you want it set back to True

[fredlo2010]

What do you mean?


I assume that this is a slip of the keyboard fingers.

it was just a copy paste that backfired.

Thanks for the catch

[GTO]

Mark / fredlo



Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = False


I assume that this is a slip of the keyboard fingers.

Tinbendr has it right with True in the 2nd statement

Not being picky but I assume you want it set back to True


ACK!:banghead:

Sorry about that, you of course are quite right. Thank you Jolivanes:hi:

[GTO]

As usual, Mark goes goes above and beyond!!!!!
:blush Well, I doubt anything I come up with is worth that, but thank you so much for the encouragement David.

BTW, I am not sure (sorry - I call it "blond memory") if I have asked before, but does Tinbendr happen to refer to working with sheetmetal?


Wow guys, thanks a lot for the help this works perfectly. I am using Mark's version. I only added a little more information to this part:


Dim as String
varIlligalChar = "[\/:*?""<>|"

If Not IsLegalName(Array(varNameFolder, varProjectFolder, varFile)(n), True) Then
MsgBox "File was not saved. Please make sure: " & vbCr _
& vbCr & Chr(149) & " Name, Folder and File have been entered" _
& vbCr & Chr(149) & " The names can not contain Special Characters: " & varIlligalChar, 0, "Empty cells"
Exit Sub
End If

I do not think I've ever used bullet points on a msgbox; that is neat!

A minor point, but you want to tack in the right/closing bracket as one of the disallowed chars.

Although totally unnecessary for what we are doing, as the function "decides" based on whether naming a sheet or file/folder, I altered it to return what string of chars is currently disallowed. Just FWIW:
Function IsLegalName(ByVal Path As String, _
IllegalCharList As String, _
Optional IsFileOrFolderName As Boolean = True _
) As Boolean

Dim sIllegalChars As String
Dim n As Long

Const MAXLEN As Long = 31
Const RB As String = "]"
Const HX As String = "History"

If Len(Path) = 0 Then Exit Function

If IsFileOrFolderName Then
sIllegalChars = "[\/:*?""<>|" ' Keep "]" seperate to use Like
Else
sIllegalChars = "[*?/\:"
End If

IllegalCharList = RB & sIllegalChars

For n = 1 To Len(Path)
If Mid(Path, n, 1) Like "[" & sIllegalChars & "]" Or Mid(Path, n, 1) = RB Then
Exit Function
End If
Next

If Not IsFileOrFolderName And (Path = HX Or Len(Path) > MAXLEN) Then
Exit Function
End If

IsLegalName = True
End Function
...adding a declaration for varIllegalChars and called like:
For n = 0 To 2
If Not IsLegalName(Array(varNameFolder, varProjectFolder, varFile)(n), varIllegalChars, True) Then
MsgBox "File was not saved. Please make sure: " & vbCr _
& vbCr & Chr(149) & " Name, Folder and File have been entered" _
& vbCr & Chr(149) & " The names can not contain Special Characters: " & varIllegalChars, 0, "Empty cells"
Exit Sub
End If
Next



Thanks you very much guys for the efforts. I would have never come up with something like that. I guess there is a long road of learning ahead of me.

Note:

Guys, I am new to this forums and I see the same people around answering questions most of the time. I am working on a big project and looking for new solutions and most of the time I face problems and I have to post questions here. How bad does it look when the same person posts too many threads?

Thanks again for the Help :)

It seems to me that you are traveling that road at a fine clip. Besides, it is mostly a quite fun road to travel, save the seemingly oft tripping on rocks I didn't see:doh: .

Just to add to what David has stated, at least for me, I have never noticed anyone having "too many" threads per se. Again, just me, but I usually enjoy, and hence am more likely to try and assist when:

If the project file contains a notable amount of code not related to the current issue, a smaller example file is attached (or several files zipped if we are trying to grab stuff from many files) instead.
Even if the question is at a formula, if it is a complex formula, an attachment or clearly layed out description of the sheet and values being used makes things so much easier to decipher. (BTW, I am pretty much worthless at formulas, but still TRY and learn)
If midway through a project, a link to a previous thread concerning the same project can be helpful sometimes, in clarifying the current issue.Mostly, I find that a descriptive thread title, and painting a good picture in the question, including example code/data or attachment seem to be the most important factors. Then it is just a matter of is I have a clue as to an answer, or at least think I could maybe learn and answer.

Thanks for the reply. I will definitively try and research and try again before posting here.

I don't want a consultant, I want to learn this. As a matter of fact I might take some VB classes next semester and see how it goes...

...For sure I will always explain what I want maybe I am thinking one thing and the result might be easier than we thought. Sometimes we have things in front of our eyes and we don't see them...

Bravo! you already qualify as the type of poster it is fun to help:clap:

LOL at the "forest for the trees" comment; ain't it the truth?!

Please note a correction, as I found another little goober of mine...
Function FixPath(Path As String, Optional StripTrailing As Boolean = False) As Boolean
Do While Len(Path) > 0 And Right(Path, 1) = Application.PathSeparator
Path = Left(Path, Len(Path) - 1)
Loop
If Len(Path) > 0 And Not StripTrailing Then Path = Path & Application.PathSeparator

If (Len(Path) >= 2 And Not StripTrailing) Or (Len(Path) > 0 And StripTrailing) Then
FixPath = True
End If
End Function
I guess I was thinking if C:\ when thinking of a minimum length of 3, but of course a folder named "x" would be legitimate...

A great day to all,

Mark

[fredlo2010]

Thanks for all the clarifications and comments.


I do not think I've ever used bullet points on a msgbox; that is neat!

I got the bullets from my book because for some reason I always forget to use the + for the buttons and the icon in the MsgBox, so I have to refer to my book.

Thanks to all of you.

[Tinbendr]

BTW, I am not sure (sorry - I call it "blond memory") if I have asked before, but does Tinbendr happen to refer to working with sheetmetal?Aircraft mechanic by trade. Did ten years specifically in sheetmetal. My handle is a throwback to Quantumlink (Q-Link C-64 service before the internet, hence the missing 'e'. They only allowed eight characters.)

Now I just push paper around.

[fredlo2010]

Hi Mark,

I know this is a closed thread but I just noticed that I had a little issue with the code. This comes up when I hit the cancel button, the no button or the close window.

If FileOrDirExists(varPath) Then

If MsgBox("The file ...\" & varNameFolder & varProjectFolder _
& varFile & " already exists. Would you like to replace it?", _
vbYesNo Or vbCritical, "Overwrite File") = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = True

End If
Else

ActiveWorkbook.SaveAs Filename:=varPath '<=========== save failed

End If

Thanks a lot

[GTO]

Hi Mark,

I know this is a closed thread but I just noticed that I had a little issue with the code. This comes up when I hit the cancel button, the no button or the close window.

If FileOrDirExists(varPath) Then

If MsgBox("The file ...\" & varNameFolder & varProjectFolder _
& varFile & " already exists. Would you like to replace it?", _
vbYesNo Or vbCritical, "Overwrite File") = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = True

End If
Else

ActiveWorkbook.SaveAs Filename:=varPath '<=========== save failed

End If

Thanks a lot

I must be missing something, but if you make it to that line, what cancel button/no button are you referring to?

[fredlo2010]

The one called by the message box. I put some notes in the code to make it clearer


'this line checks if there is a file with the same name in the same directory

If FileOrDirExists(varPath) Then

'if there is a file there then This MsgBox will show up
'this has the Yes/No/Cancel buttons.

If MsgBox("The file ...\" & varNameFolder & varProjectFolder _
& varFile & " already exists. Would you like to replace it?", _
vbYesNo Or vbCritical, "Overwrite File") = vbYes Then '<========If I hit yes the file is overwritten
'If I press no then I go to the line with the error


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = True

End If
Else

ActiveWorkbook.SaveAs Filename:=varPath '<=========== save failed

End If

Thanks a lot I tried several error handling options but I could not get it to work

[GTO]

Okay, I do not see how it can go to the code after the Else, if it has already passed the If test (on the outer If...Then...Else Statement). See if this makes sense:

' Put a Stop here, and step thru the remainder:
Stop
varPath = varPath & varNameFolder & varProjectFolder & varFile


If FileOrDirExists(varPath) Then
'If we made it here, to where the MsgBox is called, the file exists in the path supplied.
If MsgBox("The file " & varFile & " already exists. Would you like to replace it?", _
vbYesNo Or vbCritical, "Overwrite File") = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = False
End If

Else
'Thus, if the MsgBox displayed, I am not seeing how you made it here?
ActiveWorkbook.SaveAs Filename:=varPath
End If
' It would instead, end up here, and harmlessly exit the procedure
End Sub

[fredlo2010]

Hi,

This does not help.

Here is a picture of the error. I tested the original code ( no alterations by me) and I get the error also.

http://i47.tinypic.com/4hxwde.jpg

[snb]

Sub Save_snb()
if cells(2,2)="" or cells(3,2)="" or cells(4,2)="" then exit sub

on error resume next

if dir("C:\" & cells(2,2),16)="" then mkdir "C:\" & cells(2,2)
if dir("C:\" & cells(2,2) & "\" & cells(3,2),16)="" then mkdir "C:\" & cells(2,2) & "\" & cells(3,2)

ActiveWorkbook.SaveAs "C:\" & cells(2,2) & "\" & cells(3,2) & "\" & cells(4,2),51
if err.number<>0 then msgbox "check Cells B2, B3 en B4 for invalid characters"
End Sub

[fredlo2010]

Mark,

For some reason the line that triggers the MsgBox is that one. Is like the Else is validated before the If... kinda strange

If I move the actual MsgBox code Down to the Else then it works perfectly but the file exists validation turn always true ( even though its false)

If FileOrDirExists(varPath) = True Then

MsgBox "never"

Else
If MsgBox("The file ...\" & varNameFolder & varProjectFolder _
& varFile & " already exists. Would you like to replace it?", _
vbYesNo Or vbCritical, "Overwrite File") = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varPath
Application.DisplayAlerts = True
End If
End If

Actually I never get to see the "Never" MsgBox no matter what I do.

I found this link from a guy with a similar problem Link (http://answers.microsoft.com/en-us/office/forum/office_2010-excel/excel-2010-vba-run-time-error-1004-with-saveas-if/5432c143-3f5a-e011-8dfc-68b599b31bf5)

[fredlo2010]

Ok guys,

After a lot of work, try and error and by shear luck I found the issue.

1. It was mistaking ( silly of me ) the windows notification with my Msgbox (my Msgbox doesn't even have a cancel button)

Here is the difference, side by side

http://i49.tinypic.com/2jwdpy.jpg

I should have know I am politer than Excel ( do you want vs would you like :))

2. When I was creating the sample pictures to show them in my forum I tried a different string for the file name and that cleared the error. After several tries I figured out that the file name couldn't be sorter than 5 characters long.

Here is the code that was causing the issue

If Not InStrRev(varFile, ".") >= Len(varFile) - 4 Then
varFile = varFile & Mid(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, "."))
End If

I don't really know what the problem is, I read something about excel reference file verification. Here is my solution

If Not InStrRev(varFile, ".") >= Len(varFile) - (-1) Then
varFile = varFile & Mid(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, "."))
End If

Thanks a lot for the help guys :)

[damursocute]

Anyone reading this thread, please go back 2 posts and see the sensible answer by snb. It is my first time here and I wanted to check my SaveAs method. :banghead: To avoid the need for pain medication, I will check for VBAX Mentor posts in future.