Saving to a Dropbox location

[kallabungo]

Hi all, i'm having a problem trying to use my existing macro coding to work across multiple computers accessing the same files hosted on Dropbox.

Currently I have created macros that will generate consecutive invoice numbers and then export the file as a PDF to a destination file with a prefix before the invoice number, which has worked flawlessly for years.

Now, I need to relocate these files to Dropbox so that multiple users can access from different computers. I have no doubts that the coding required in this is very simple but I have little to no knowledge of VBA and really need some help from someone that knows more than me.

So, this is the code that I am currently using.

Sub NextInvoice()
Range("H13").Value = Range("H13").Value + 1
Range("B24:H43, H15").ClearContents
End Sub
Sub SaveInvWithNewName()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\East Point Metals.DESKTOP-HFIK217\Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
NextInvoice
End Sub

Now the problem im experiencing is related to this;

C:\Users\Stuart\Dropbox\Invoicing\Credit Notes
C:\Users\Home\Dropbox\Invoicing\Credit Notes

These are 2 other folder paths from the other computers accessing these files. I've done some hard research and manipulation of code but cannot get it to work.

Ideally I'm looking for some coding that will pull the correct folder path off the current users computer and will insert it before the \Dropbox\Invoicing\Credit Notes so everything goes where it should.

Really appreciate any help that may be received.

 

[Anthony47]

The code you need to use Environ("UserProfile")
For example

FullFileName = Environ("UserProfile") & \Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF"

The use FullFileName in your ActiveSheet.ExportAsFixedFormat line.

Bye

 

[kallabungo]

Hi Anthony thanks for taking the time to reply. I have tried so many different combinations of using the code you provided and keep getting "expected: end of statement" errors.

Are you able to help any further at all?

 

[Anthony47]

I guess you are misung the "underscore" (_) character; if you list your macro probably we can help.
Copy the code from your macro and paste it into your next message (no retyping, please)

Bye

 

[kallabungo]

Sub NextInvoice()
Range("H13").Value = Range("H13").Value + 1
Range("B24:H43, H15").ClearContents
End Sub
Sub SaveInvWithNewName()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"FullFileName = Environ("UserProfile") & \Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
NextInvoice
End Sub

 

[kallabungo]

Well I have found the easiest way to get around this problem, although a little crude.

Just move Dropbox install location to C:\Dropbox - then do the same on the other computers that are accessing the files as their main hard drive are also C:\

Super easy!

 

[Anthony47]

Instead of
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"FullFileName = Environ("UserProfile") & \Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

use

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Environ("UserProfile") & "\Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Bye

 

[kallabungo]

I copied and pasted the code exactly as you said however i get a runtime 1004 error, the document is unable to save.

Thanks for your time on this though, really appreciate it.

 

[Anthony47]

Well, I answered to your question:
"Ideally I'm looking for some coding that will pull the correct folder path off the current users computer and will insert it before the \Dropbox\Invoicing\Credit Notes so everything goes where it should"

I think the problem is with the user that saves in "C:\Users\East Point Metals.DESKTOP-HFIK217\Dropbox\Invoicing\etc etc": does his UserProfile point to "C:\Users\East Point Metals.DESKTOP-HFIK217"?

For debug purpose, add this line in this position:

Debug.Print Environ("UserProfile") & "\Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF"   '<< THIS LINE IN THIS POSITION
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Environ("UserProfile") & "\Dropbox\Invoicing\Credit Notes\Crn" & Range("H13").Value & ".PDF", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Then try running the code.
When the error arise use the option "Debug" to see which is the highligthed instruction.
At that point, press Contr-g to open the "VBA Immediate" window: see what is the file name listed and check that the path exists.
Insert your findings in your next message: the line in error is the ActiveSheet.ExportAsFixedFormat one? Which is the file name shown and does the path exists?

Bye