Hi, I'm searching for a way to add multiple r...

references
(Edgar Butron) #1

Hi, I’m searching for a way to add multiple records with one entry. I currently have an excel macro that will allow me to create a new [Repair Order], select the respective [Operations], and add them to a production table with each separate Operation record showing on its own row.

I’d do this action with my current macro and link it to the Onedrive Excel spreadsheet that Appsheet fills out, but I can’t find a way to link both workbooks and Appsheet doesn’t support macro-enabled workbooks… yet. But hopefully soon.

Is this possible with Appsheet? and if not, are their any solutions that would be recommended to get a similar result?

I’ve attached a screen shot of the current macro for reference.

(Edgar Butron) #2

I forgot to mention, I need this to show up in my spreadsheet, not just as a virtual display.

(Edgar Butron) #3

yeesssss!! So I believe i’ve tried something similar. I have the xlsx spreadsheet in Onedrive, and a xlsm spreadsheet on our local network. Both identical other than the macro part. What you see in the photo there is the xlsm spreadsheet that throws all the information in the spreadsheet. However we’ve ran vba code to copy that into xlsx spreadsheet, but i’m having trouble getting into that Ondrive spreadsheet to fully automate this action. Basically i can’t find a good path to have the macro pull up the Ondrive file and update the spreadsheet.

If you could please run me through the vba code I would owe you big! Thanks in advance.

(Edgar Butron) #4

@Segolene_Sansom.

See above for Help.

(Edgar Butron) #5

…not sure how you wanted to go about sharing this information. But if you’d like to share the vba code that would go a long way into reviewing the code to see if it’ll work for us.

@Segolene_Sansom

(Segolene Sansom) #6

ok so all the code will be written between “//” 1) Make sure your xlsm and xlsx files have exactly the same tabs, tables and column structure

  1. Add an extra tab on your macro for versioning to troubleshoot data in the future and also helps to save as a point in time copy at the end. Use the columns in order: “Date”, “Version Number”, “Reason for Update”, “Updated By”

Create a new module in the vba editor and a new public sub ( i called mine “ImportDataFromAppsheetWorkbook”)

  1. Turn off your screen updating //Application.ScreenUpdating = False//

  2. I put in a check to see whether they want to update the data or not: //'Select whether you want to update the data

Dim answer As Integer

answer = MsgBox(“Do you want to update the data from the app spreadsheet?”, vbYesNo + vbQuestion, “Import Data”)

If answer = vbNo Then

Exit Sub

Else

'Continue with procedure

End If//

6)Select your 2 workbooks - the xlsx needs to be accessible by filepicker //'Select workbook to copy data from and into

Dim wkbM As Workbook

Set wbkM = ThisWorkbook

Dim wbkX As Workbook

Dim wbkXPick As Office.FileDialog

Dim wbkXPath As String

Set wbkXPick = Application.FileDialog(msoFileDialogFilePicker)

With wbkXPick

.AllowMultiSelect = False

.Title = “Please select the file”

.Filters.Clear

.Filters.Add “Excel Workbook”, “*.xlsx”

If .Show = -1 Then

wbkXPath = Dir(.SelectedItems(1))

Else

MsgBox (“No File is selected”)

Exit Sub

End If

End With

Workbooks.Open (wbkXPath)

Set wbkX = Workbooks(wbkXPath)//

  1. Initialise your tab values - repeat the below for as many tabs as you want to copy across. Replace the letters after the M or X to letters representing your tab name //'Initialise tab values

'Macro Workbook

Dim MOI As Worksheet

Set MOI = wbkM.Sheets(“Outlet Information”)

Dim MOILO As ListObject

Set MOILO = MOI.ListObjects(1)

'Plain Excel Workbook

Dim XOI As Worksheet

Set XOI = wbkX.Sheets(“Outlet Information”)

Dim XOILO As ListObject

Set XOILO = XOI.ListObjects(1)//

  1. If you want to use the versions add this in too

//Dim MVN As Worksheet

Set MVN = wbkM.Sheets(“Version Numbering”)

Dim MVNLO As ListObject

Set MVNLO = MVN.ListObjects(1)

Dim MVNLOR As ListRow

Set MVNLOR = MVNLO.ListRows.Add//

  1. Copy tab data 1 by 1 - Repeat below for as many tabs as you may have //

'Outlet Information

XOILO.DataBodyRange.Copy

MOILO.DataBodyRange.PasteSpecial

'Area Information

XAILO.DataBodyRange.Copy

MAILO.DataBodyRange.PasteSpecial//

  1. Update versions //'Update Versions

With MVNLOR

.Range(1) = Now()

.Range(2) = WorksheetFunction.Min(MVNLO.ListColumns(2).Range) + 1

.Range(3) = InputBox(“What is the reason for updating the data?”)

.Range(4) = InputBox(“Who updated the data?”)

End With//

  1. Break links between the 2 excels so that you have clean data

// 'Break links between 2 workbooks

Dim ExternalLinks As Variant

Dim x As Long

'create an array of all external links stored in workbook

ExternalLinks = wbkM.LinkSources(Type:=xlLinkTypeExcelLinks)

'Loop Through each external link in workbook and break it

For x = 1 To UBound(ExternalLinks)

wbkM.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks

Next x//

  1. Run any macro you would like to run by calling it

  2. Copy your data back from the xlsm to the xlsx by repeating steps 9 and 11 but exchanging wbkX and wbkM everywhere

  3. Close your base xlsx sheet //

wbkX.Close//

  1. Reactivate the screen updating //Application.ScreenUpdating = True//

and you’re done. If there’s something in this code you don’t understand or want more clarification on pls let me know. Otherwise hope it helps.

(Stephen Mattison) #7

@Segolene_Sansom TYTYTY!

(Segolene Sansom) #8

@Stephen_Mattison you’re most welcome

(Segolene Sansom) #9

@Stephen_Mattison just wondering how it went implementing that?

(Edgar Butron) #10

Still trying to implement mine. Ive copied most of the code over and integrated it into my application. Im wanting to finish tomorrow after work and will let you know how it goes. Ill keep you updated @Segolene_Sansom and thank you so much for the help this far.

(Aleksi Alkio) #11

I’m afraid this is not yet possible. It’s in the todo list, but it will take some time.

(Segolene Sansom) #12

to get around the macro limitations of appsheet - i’ve created 2 identical in format excels one xlsx and the other xlsm.

Then whenever i want to run the macro, i have set a code that allows me to open both files copies the data from xlsx to xlsm, run the macro and then copy the data back to xlsx. am happy to walk you through the vba code i used for this

(Stephen Mattison) #13

@Segolene_Sansom

Wow, great work!

Show us! TY!

(Segolene Sansom) #14

@Stephen_Mattison what’s the best way? Copy sequence of code?

(Stephen Mattison) #15

@Segolene_Sansom Sorry, I don’t have answers for something this complicated.

Aleksi is the Master!

(Aleksi Alkio) #16

@Stephen_Mattison I believe Segolene is asking how you would like to have the information she did it :slight_smile:

(Stephen Mattison) #17

@Aleksi_Alkio

Ah, OK!

@Segolene_Sansom

Please show us however you see fit! TYVM!

(Aleksi Alkio) #18

:smiley: