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)//
- 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)//
- 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//
- 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//
- 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//
- 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//
-
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//
- 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.
LinkedIn
Twitter
