SaveAs error message help!

woods2007

Board Regular
Joined
Aug 29, 2007
Messages
57
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi

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

Rich (BB code):
'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
 
Upvote 0
Hi

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

Rich (BB code):
'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
 
Upvote 0
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:

Code:
?"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)
 
Upvote 0
From immediate window:
?"S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data " & mytemplate2 & "\" & FN
S:\ACTIVE JOBS\CURRENT JOBS\Sytner\sytner data 07112011\BMWMOT09112011.csv
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,587
Members
452,860
Latest member
jroberts02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top