Solved

How do I open files saved in Microsoft Onedrive using vba

Posted on 2014-12-22
7
3,312 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now