Solved

How do I open files saved in Microsoft Onedrive using vba

Posted on 2014-12-22
7
6,144 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Accepted Solution

by:
Michael Fowler 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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 58

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 58
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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

635 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