How to Input data into a Excel worksheet, that is cloud based

Hello everyone,

Everyday we collect some data and input it into an excel sheet which we keep on Sharepoint, which is cloud based.

I currently have a script that pulls that information and creates an excel sheet and then I copy it over.

Is it possible to have it directly place it into the cloud based one?

I used the sample template to create the script I currently use.

Sorry it's a bit long, I need to refine it.

Sub Main
'*****************************************************************************************************
' 5250 DemoHost to Excel Data Transfer ( Basic )
'
' This example connects to the Netmanage 5250Demohost.netmanage.com, brings up a data screen,
' opens a new Excel workbook and transfers data to Excel
'*****************************************************************************************************

'*****************************************************************************************************
' Define all variables
'*****************************************************************************************************
Dim XLApp As Object ' Excel Application Object
Dim XLBook As Object ' Excel WorkBook Object
Dim XLSheet As Object ' Excel WorkSheet object
Dim XLRange As Object ' Excel Range Object

Dim GoodData ' Variant to hold data value for transfer
Dim ScreenText As String ' Text read from host screen
Dim Row As Integer ' Screen Row
Dim Col As Integer ' Screen Column
Dim CurrentExcelCell As String ' String to hold Excel Cell reference, we are using the "A1" addressing

'*****************************************************************************************************
' Bring up Excel application and open a new Workbook
'*****************************************************************************************************

Set XLApp = CreateObject("Excel.Application") ' Create an EXCEL application
Set XLBook = XLApp.Workbooks.Add ' Create an EXCEL Workbook
Set XLSheet = XLBook.Worksheets("Sheet1") ' Create an EXCEL Worksheet
Set XLRange = XLSheet.Range("A1:A5") ' Create an EXCEL Range
XLRange.ColumnWidth = 17 ' Set Column Width
XLSheet.Activate ' Activate the Worksheet

'*****************************************************************************************************
' This section grabs the data from the host and places it in the appropriate Excel cell
' The data point screen locations are set manually into the program
'*****************************************************************************************************

EMConnect "A"
EMSetCursor 20, 7
EMSendKey "dspsyssts<Enter>"
EMReadScreen ScreenText,5,1,72 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("A1") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,7,5,73 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("A2") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,4,6,35 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("A3") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,6,7,33 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("A4") = GoodData ' Write data point from screen to Excel
EMSendKey "<PF3>"

EMConnect "B"
EMSetCursor 20, 7
EMSendKey "dspsyssts<Enter>"
EMReadScreen ScreenText,5,1,72 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("B1") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,7,5,73 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("B2") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,4,6,35 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("B3") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,6,7,33 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("B4") = GoodData ' Write data point from screen to Excel
EMSendKey "<PF3>"

EMConnect "C"
EMSetCursor 20, 7
EMSendKey "dspsyssts<Enter>"
EMReadScreen ScreenText,5,1,72 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("C1") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,7,5,73 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("C2") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,4,6,35 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("C3") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,6,7,33 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("C4") = GoodData ' Write data point from screen to Excel
EMSendKey "<PF3>"

EMConnect "D"
EMSetCursor 20, 7
EMSendKey "dspsyssts<Enter>"
EMReadScreen ScreenText,5,1,72 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("D1") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,7,5,73 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("D2") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,4,6,35 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("D3") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,6,7,33 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("D4") = GoodData ' Write data point from screen to Excel
EMSendKey "<PF3>"

EMConnect "E"
EMSetCursor 20, 7
EMSendKey "dspsyssts<Enter>"
EMReadScreen ScreenText,5,1,72 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("E1") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,7,5,73 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("E2") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,4,6,35 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("E3") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,6,7,33 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("E4") = GoodData ' Write data point from screen to Excel
EMSendKey "<PF3>"

EMConnect "F"
EMSetCursor 20, 7
EMSendKey "dspsyssts<Enter>"
EMReadScreen ScreenText,5,1,72 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("F1") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,7,5,73 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("F2") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,4,6,35 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("F3") = GoodData ' Write data point from screen to Excel
EMReadScreen ScreenText,6,7,33 ' Read the data point
GoodData = Str(ScreenText) ' Convert text to variant
XLSheet.Range("F4") = GoodData ' Write data point from screen to Excel
EMSendKey "<PF3>"

XLSheet.Application.Visible = True ' Show the EXCEL sheet

End Sub

#Rumba

Hello ErikAllen,
better long code then no code !!! The code helps to understand what and how you do it.

If your local Excel version allows you to save to Sharepoint then it can be done with the Workbook.SaveAs Method.

Add at the end of your code before you close and clean up the Excel Object:

XLBook.SaveAs Filename:=sPath & sFile & ".xlsx"

where sPath and sFile are declared as follows: (you need to replace it with your path to sharepoint)

sPath = "Yourcompany-my.sharepoint.com/.../Documents"
sFile = "RumbaRocks"