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.
I forgot to mention, I need this to show up in my spreadsheet, not just as a virtual display.
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.
See above for Help.
โฆ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.
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
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โ)
Turn off your screen updating //Application.ScreenUpdating = False//
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)//
'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)//
//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//
'Outlet Information
XOILO.DataBodyRange.Copy
MOILO.DataBodyRange.PasteSpecial
'Area Information
XAILO.DataBodyRange.Copy
MAILO.DataBodyRange.PasteSpecial//
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//
// '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//
Run any macro you would like to run by calling it
Copy your data back from the xlsm to the xlsx by repeating steps 9 and 11 but exchanging wbkX and wbkM everywhere
Close your base xlsx sheet //
wbkX.Close//
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.
@Segolene_Sansom TYTYTY!
@Stephen_Mattison youโre most welcome
@Stephen_Mattison just wondering how it went implementing that?
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.
Iโm afraid this is not yet possible. Itโs in the todo list, but it will take some time.
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 whatโs the best way? Copy sequence of code?
@Segolene_Sansom Sorry, I donโt have answers for something this complicated.
Aleksi is the Master!
@Stephen_Mattison I believe Segolene is asking how you would like to have the information she did it
User | Count |
---|---|
43 | |
27 | |
24 | |
23 | |
13 |