0

Morning, Afternoon, Evening.

What I'm trying to do. I'm trying to save a pdf file from an excel worksheet to a folder shortcut for a shared OneDrive account. The workbook sits in the parent folder to where I want to save the pdf.
The workbook sits in C:\Users\iangre\OneDrive\Shared Documents\BI\Forms\Quality Assurance_Ventilation
I'm trying to save to C:\Users\iangre\OneDrive\Shared Documents\BI\Forms\Quality Assurance_Ventilation\Save

The issue. It keeps throwing up a 1004 run-time error (which I understand is next to useless for diagnosing the problem).
The workbook will be used by others so I need to include the user in the save location. I've tried various ways of capturing the local file path, but all generate the 1004 error when saving.

The vb line I'm using

ref = CurDir() & "\Save\" & sh01.Cells(rw2, 4).Value & " " & sh01.Cells(rw2, 5).Value & ".pdf"`
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ref, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

but I get the runtime error.

The closest workaround I can find is

ref = Application.ActiveWorkbook.Path & "\Save\" & sh01.Cells(rw2, 4).Value & " " & sh01.Cells(rw2, 5).Value & ".pdf"

This generates the "https://" filepath and saves the file but I then have to sign in every time -not ideal.

Incidentally it works if I manually save the pdf to the shortcut, but if I then record the actions it generates the same error.

As ever, any help gratefully received as I'm really stumped on this one.
Thanks. Ian

FunThomas
  • 23,043
  • 3
  • 18
  • 34
Ian
  • 113
  • 1
  • 10
  • Have you checked that `ref` contains a valid file name? Dump it to the immediate window, use Copy&Paste. Now switch to Excel and save the workbook as PDF manually, using the string that you just copied as filename. Does this work? Also note that `CurDir` can show to different folders, it is *not always* the folder where the actual workbook is stored. – FunThomas Mar 02 '23 at 11:16
  • If I try to save the pdf using the ref file path I get the "Path does not exist" message. If I record a macro and save the file to the shortcut, it generates a filepath identical to the ref file path and successfully saves. If I then play that exact recorded macro, it throws up a 1004 error. In this instance Application.ActiveWorkbook.Path and CurDir gives me the same file path. – Ian Mar 03 '23 at 08:38

0 Answers0