Using Dropbox source data for a Connection in Excel with different machines

Topic

New Reply

I have a Weather Station that’s set up to download data as a CSV file to a sub-folder of my local Dropbox folder. This then gets Sync’d to the Dropbox server and through that to my other machine that also has a Dropbox folder.

I use the data in an Excel Spreadsheet which has a data connection to import the CSV data and then does the manipulation and provides a dashboard so I can get weather for any day in the last month.

The issue I have is that my Laptop gets its weather data from Dropbox at C:\Users\ACER\Dropbox\WeatherData

whereas my Desktop would get it from   C:\Users\PC\Dropbox\WeatherData

With each machine having its own USERNAME, if I store the Excel spreadsheet with the correct data connection saved for one machine, it won’t be correct for the other machine

Ideally, I would like each machine to connect to the data in its own local Dropbox folder, without the users having to find the data themselves

In other words, if I have stored the Data Connection source, and it’s held within Worksheets(8).QueryTables(“_EasyWeather”).Connection can I have that use the correct USERNAME for each machine based on the address of the Dropbox folder?

Reply | Quote

Replies

WSG,

You have a couple of options here.

• You can retrieve the Environment Variable UserProfile to retrieve the correct base path for the machine the Excel is running on. You’d then use this in your DataConnection statement: Environ(“UserProfile”)\Dropbox\WeatherData.
• Use the Subst command (old school here) to assign your name to a drive letter on each machine then have the excel reference this drive letter, e.g.
  Subst W: %UserProfile%, then use that in your data connection statement, e.g. W:\Dropbox\Weatherdata.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote