Ian
Ian

Reputation: 129

VBA to save to a shared OneDrive account via local shortcut

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

Upvotes: 0

Views: 598

Answers (0)

Related Questions