Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I open files saved in Microsoft Onedrive using vba

Posted on 2014-12-22
7
Medium Priority
?
7,049 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 672 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 664 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 664 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 49

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

671 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