Solved

How do I open files saved in Microsoft Onedrive using vba

Posted on 2014-12-22
7
3,715 Views
Last Modified: 2015-01-24
Hi Experts,
I use the code below to open a file, in an ACCESS application, that's saved in "C:\Schedules\" file path.  The app users will start saving the spreadsheets in Microsoft OneDrive.  How do I rewrite my code so that it reads the files from Microsoft OneDrive?  Thanks in advance.

 
  Const cstrFolder As String = "C:\Schedules\"
  Dim i As Long, x As Long, lng As Long
  Dim xlApp As Object
  Dim xlWrk As Object
  Dim xlSheet As Object
  Dim sql As String
  Dim strExt As String, strFile As String, strTable As String
  Dim strFirstName As String, strLastName As String, strMonth As String
  
  
  Set xlApp = VBA.CreateObject("Excel.Application")
  xlApp.Visible = False

  strExt = ".xls"
  lng = Len(strExt)
  strFile = Dir(cstrFolder & "*" & strExt)

  If Len(strFile) = 0 Then
    MsgBox "No Files Found"
  Else
    Do While Len(strFile) > 0

        Set xlWrk = xlApp.Workbooks.Open(cstrFolder & strFile) 
 

Open in new window


mrotor
0
Comment
Question by:mainrotor
7 Comments
 
LVL 23

Accepted Solution

by:
Michael74 earned 168 total points
ID: 40514399
I haven't tries it but it is my understanding that if you download and install OneDrive on your computer and you can then get the file path to use in

Set xlWrk = xlApp.Workbooks.Open
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 166 total points
ID: 40514413
I assume you are referring to reading the Excel files ?

This might work.
You would need to Sync OneDrive using the OneDrive for Business app, which may be resident on your PC if you have Office 2013. Or, I believe you can download it free from Microsoft.
Once in operation, OneDrive will keep a local folder(s) in sync with OneDrive folders on the web. So, when a user makes a change to an Excel file (in the OneDrive sync folder), it will automatically sync up with your local folder. I've been using this scheme for about 3 months for files that I use across 2-3 laptops. It works very well and is FAST.

Just not sure if OneDrive sync works on the free OneDrive account.  I have an Office 365 Enterprise E3 account.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 166 total points
ID: 40514728
Onedrive is a web site service and as such, you'll need to use its API interface to work with files and folders.


Working with Microsoft OneDrive folders and files
http://msdn.microsoft.com/en-us/library/hh826531.aspx

From VBA, you would do this with REST calls using the XML doc lib.

The complete API docs start here:

http://msdn.microsoft.com/en-us/library/hh826521.aspx

Below is an example of calling a web service.  Basically, you build a request string, send it, then look at the response that comes back.  That boils down to this:


                Dim objRequest As New MSXML2.XMLHTTP
                objRequest.Open "POST", strPostURL, False
                objRequest.Send strPostSting
                strPostResponse = objRequest.responseText

 Note that the example below is not working with OneDrive, but simply an example of how you call a web service from VBA.   I have not worked with OneDrive myself.

Jim.
                ' Capture the CC

                  ' Set the correct URL
                  'strPostURL = "https://test.authorize.net/gateway/transact.dll"
110               strPostURL = "https://secure.authorize.net/gateway/transact.dll"
                  'strPostURL = "https://developer.authorize.net/tools/paramdump/index.php"

120               strPostSting = ""
130               strPostSting = strPostSting & "x_login=" & URLEncode(strAPILogin) & "&"
140               strPostSting = strPostSting & "x_tran_key=" & URLEncode(strTransactionKey) & "&"
                  'For debugging.
                  'strPostSting = strPostSting & "x_test_request=" & URLEncode("TRUE") & "&"
150               strPostSting = strPostSting & "x_version=" & URLEncode("3.1") & "&"
160               strPostSting = strPostSting & "x_delim_data=" & URLEncode("TRUE") & "&"
170               strPostSting = strPostSting & "x_delim_char=" & URLEncode("|") & "&"
180               strPostSting = strPostSting & "x_relay_response=" & URLEncode("FALSE") & "&"
190               strPostSting = strPostSting & "x_email_customer=" & URLEncode("FALSE") & "&"

200               strPostSting = strPostSting & "x_type=" & URLEncode("PRIOR_AUTH_CAPTURE") & "&"
210               strPostSting = strPostSting & "x_trans_id=" & URLEncode(rs!CCTransactionID) & "&"

                  ' Additional fields can be added here as outlined in the AIM integration
                  ' guide at: http://developer.authorize.net
220               strPostSting = left(strPostSting, Len(strPostSting) - 1)

                  ' We use xmlHTTP to submit the input values and record the response
                  Dim objRequest As New MSXML2.XMLHTTP
230               objRequest.Open "POST", strPostURL, False
240               objRequest.Send strPostSting
250               strPostResponse = objRequest.responseText
                  'Debug.Print strPostResponse
260               Set objRequest = Nothing

                  ' the response string is broken into an array using the specified delimiting character
270               arrResponse = Split(strPostResponse, "|", -1)

280               If arrResponse(0) = 1 Then
                      ' Amount was captured.
                      ' Update order import tracking table
290                   strCommand = "UPDATE tblOrdImportTracking SET CCCapturedAt = '" & Now() & " ' WHERE JobNumber = " & !JobNumber & " AND ExportersOrderNumber = '" & !ExportersOrderNumber & "'"
300                   cnn.Execute strCommand, lngRecordsAffected, adCmdText
310                   If lngRecordsAffected <> 1 Then Stop
320               Else
330                   Stop
                      ' Not captured for some reason - email IT alert list.

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 57
ID: 40514730
By the way, here's the start of the Live SDK:

http://msdn.microsoft.com/en-us/library/dn631819.aspx

You'll want to look at "Using the REST API", which has things like how to sign onto a One Drive account.

Also the section "Move, copy, create, or delete a file or folder (REST)"

Jim.
0
 

Author Comment

by:mainrotor
ID: 40521347
Thank you all for your suggestions.  I will try them this coming week and post my results.

mrotor
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40567976
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Creating and Managing Databases with phpMyAdmin in cPanel.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now