SaveAs error message help!

[woods2007]

Hi,

I keep getting the below message:

Method 'SaveAs' of object '_workbook' Failed

Can anyone show me the error of my way?

Much appreciated!


...'Save file in Correct folder

Dim mytemplate2 As String

' Input box to help specify new weekly folder
mytemplate2 = InputBox("Input Date file recieved in 'SPACE'DDMMYYYY format", "Save to correct folder location")


FN = WSN.Cells(2, 8) & ".csv"
'FP = "S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data & mytemplate2 \"


WBN.SaveAs Filename:="S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data & mytemplate2\" & FN, FileFormat:= _
xlCSV




'WBN.SaveAs FP & FN

'WBN.Close SaveChanges:=False

 

[Richard Schollar]

Hi

Looks like you aren't incorporating your folder into the path:

'this:
WBN.SaveAs Filename:="S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data & mytemplate2\" & FN, FileFormat:= _
xlCSV

 
'should probably be something like this:
WBN.SaveAs Filename:="S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data " & mytemplate2 & "\" & FN, FileFormat:= _
xlCSV

Amendments in red

 

[woods2007]

Hi

Looks like you aren't incorporating your folder into the path:

'this:
WBN.SaveAs Filename:="S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data & mytemplate2\" & FN, FileFormat:= _
xlCSV

 
'should probably be something like this:
WBN.SaveAs Filename:="S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data " & mytemplate2 & "\" & FN, FileFormat:= _
xlCSV

Amendments in red

Thanks for that.

I made the amendments but it still gave me a run time error 1004.

The full file string should say:

S:\ACTIVE JOBS\CURRENT JOBS\Sytner\email data
\sytner data 07112011\BMWMOT09112011.csv

I have included my full code below if it helps.

Sub BMW_MOT_Pop_test()
'

Dim WBO As Workbook
Dim WBN As Workbook
Dim WSO As Worksheet
Dim WSN As Worksheet




' BMW Macro

'1. Copy data into the landingpad

ActiveSheet.Cells.Select


Selection.Copy
'
Workbooks.Open Filename:= _
"S:\ACTIVE JOBS\CURRENT JOBS\Sytner\EmailTemplates\BMW.xlsx"

Sheets("Landingpad").Cells(1, 1).Select

ActiveSheet.Paste
Application.CutCopyMode = False



'2. find final row

Dim FinalRow As Long

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

'3. check columns and copy/paste into template
If Sheets("landingpad").Cells(1, 1) <> "Loc" Then MsgBox "check source file Column Header Row 'Loc'", vbCritical, "Issue Alert- Non-Matching Header!"

Sheets("Landingpad").Range(Cells(2, 1), Cells(FinalRow, 1)).Copy Destination:=Sheets("BMWMOT").Cells(3, 1)


If Sheets("landingpad").Cells(1, 3) <> "Salute" Then MsgBox "check source file Column Header Row 'Salute'", vbCritical, "Issue Alert- Non-Matching Header!"
Sheets("landingpad").Range(Cells(2, 3), Cells(FinalRow, 3)).Copy Destination:=Sheets("BMWMOT").Cells(3, 2)

If Sheets("landingpad").Cells(1, 13) <> "Registration" Then MsgBox "check source file Column Header Row 'Registration'", vbCritical, "Issue Alert- Non-Matching Header!"
Sheets("landingpad").Range(Cells(2, 13), Cells(FinalRow, 13)).Copy Destination:=Sheets("BMWMOT").Cells(3, 3)

If Sheets("landingpad").Cells(1, 17) <> "MOT_due" Then MsgBox "check source file Column Header Row 'MOT_due'", vbCritical, "Issue Alert- Non-Matching Header!"
Sheets("landingpad").Range(Cells(2, 17), Cells(FinalRow, 17)).Copy Destination:=Sheets("BMWMOT").Cells(3, 8)

If Sheets("landingpad").Cells(1, 18) <> "Email" Then MsgBox "check source file Column Header Row 'Email'", vbCritical, "Issue Alert- Non-Matching Header!"
Sheets("landingpad").Range(Cells(2, 18), Cells(FinalRow, 18)).Copy Destination:=Sheets("BMWMOT").Cells(3, 10)

'End If


'MsgBox = vbAbort("Column Header does not match. check source data file header!")

'6. fill down

Sheets("BMWMOT").Activate
Set WBO = ActiveWorkbook


Set WSO = Sheets("BMWMOT")


Dim FinalRow2 As Long

FinalRow2 = WSO.Cells(Rows.Count, 1).End(xlUp).Row



Range(WSO.Cells(3, 4), WSO.Cells(FinalRow2, 8)).FillDown


'7. template type

Dim mytemplate As String



mytemplate = InputBox("Input send date in DDMMYYYY format", "user.customattribute.templatetype")



WSO.Cells(3, 9) = "BMWMOT" & mytemplate



WSO.Range(WSO.Cells(3, 9), WSO.Cells(FinalRow2, 9)).FillDown


Set WBN = Workbooks.Add(template:=xlWBATWorksheet)
Set WSN = WBN.Worksheets(1)

WSN.Name = "BMWMOT"

WSO.Range(WSO.Cells(1, 1), WSO.Cells(FinalRow2, 10)).Copy Destination:=WSN.Cells(1, 1)

'clean up

WSN.Cells.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection
.Interior.Pattern = xlNone
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone

End With


'Save file in Correct folder


Dim mytemplate2 As String


mytemplate2 = InputBox("Input Date file recieved in 'SPACE'DDMMYYYY format", "Save to correct folder location")


FN = WSN.Cells(2, 8) & ".csv"
'FP = "S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data & mytemplate2 \"


WBN.SaveAs Filename:="S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data " & mytemplate2 & "\" & FN, FileFormat:= _
xlCSV




'WBN.SaveAs FP & FN

'WBN.Close SaveChanges:=False




End Sub

 

[Richard Schollar]

When the macro bugs out, can you click Yes to debug and in the Immediate Window of the VBE (open with Ctrl+G if not already open) type in the following (type it exactly, including the leading ?) and press Enter at the end:

?"S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data " & mytemplate2 & "\" & FN

and paste into your reply what the above line returns to the Immediate Window. This will be filename and path you are trying to save to. You need to confirm that:

1. The path exists exactly as it appears in this string
2. The filename is valid (ie doesn't contain any excluded characters)

 

[woods2007]

From immediate window:
?"S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data " & mytemplate2 & "\" & FN
S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data 07112011\BMWMOT09112011.csv

 

[Richard Schollar]

Check the path very carefully and confirm that it does exist. It needs to be exact ie all folder names need to be exactly as they appear in the path.

 

[woods2007]

Doh! missed a sub folder out in the file string!

Thanks for your help!