VBA  Error

jmacalpine
jmacalpine used Ask the Experts™
on
I have a VBA script to run.. Get an error 5 When I try to run..Upon debugging, I am told that the Line (I've underlined it) below is using the wrong argument... I've tried a few different ways to declare that worksheet as the one I need but to no avail.

Thoughts?


Sub Tally()
Dim subj As String
subj = Worksheets("Bol Breakdown").Range("R1").Value
company = Worksheets("New").Range("N1").Value
filesformat = "PDF\Tally Sheets"
livescrap = Worksheets("New").Range("N3").Value
yeardate = Worksheets("New").Range("N4").Value
monthdate = Worksheets("New").Range("N5").Value
pathtosaveto = "S:\ARC\" & company & "\" & filesformat & "\" & yeardate & "\" & monthdate & "\" & subj

Worksheets("BOL Breakdown").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pathtosaveto, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Well I don't know what is in your "pathtosaveto" but otherwise your code works for me. If the sheet name were incorrect you'd get a Subscript out of range error.
jmacalpine

Author

Commented:
pathtosaveto = "S:\ARC\" & company & "\" & filesformat & "\" & yeardate & "\" & monthdate & "\" & subj
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Here is the code I used. Try Dimming all your variables. When I ran it the first time it told me the PDF couldn't be created and I assume that's because I don't have that path. When I changed line 15 it worked fine.

Dim subj As String
Dim company As String
Dim filesformat As String
Dim yeardate As String
Dim monthdate As String
Dim pathtosaveto As String

subj = "Test" 'Worksheets("Bol Breakdown").Range("R1").Value
company = "ML Inc." 'Worksheets("New").Range("N1").Value
filesformat = "PDF\Tally Sheets"
'livescrap = Worksheets("New").Range("N3").Value
yeardate = "2015" 'Worksheets("New").Range("N4").Value
monthdate = "June" 'Worksheets("New").Range("N5").Value
'pathtosaveto = "C:\temp\" & company & "\" & filesformat & "\" & yeardate & "\" & monthdate & "\" & subj
pathtosaveto = "C:\temp\test"
Worksheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pathtosaveto, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Open in new window

Surone1
Commented:
error 5 is permission denied. you probably want to create the folder(s) you are trying to write to before actually trying to write to them.
eg.
on error resume next
MkDir "S:\ARC\" & company 
MkDir "S:\ARC\" & company & "\" & filesformat 
MkDir "S:\ARC\" & company & "\" & filesformat & "\" & yeardate 
MkDir "S:\ARC\" & company & "\" & filesformat & "\" & yeardate & "\" & monthdate 
On Error GoTo 0

Open in new window


the comnpany and such should not contain characters that cannot be used in a directory name. this code assumes the S:\ARC directory exists

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial