Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I open files saved in Microsoft Onedrive using vba

Posted on 2014-12-22
7
Medium Priority
?
7,749 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:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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 blog post, we’ll look at how using thread_statistics can cause high memory usage.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

885 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