News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Creating Access user interface from existing Excel VBA Framework

    Posted on WSmrwowery Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Access and database help Creating Access user interface from existing Excel VBA Framework

    This topic contains 1 reply, has 2 voices, and was last updated by  RetiredGeek 2 years, 7 months ago.

    • Author
      Posts
    • #508384 Reply

      WSmrwowery
      AskWoody Lounger

      Hi All,

      I have an Excel workbook that contains 30 tabs with the Userface as shown in the photo. The data from each tab is compiled in the Data tab as shown in the photo as well. I am attempting to create a database in Access to store all of this data as there are 8 different people using the worksheet and each person saves as and names the spreadsheet differently. This results in several spreadsheets in different locations and I have to manually copy and paste the data into one master spreadsheet for analytics to be run.

      Does anybody have an idea of how I can set this up? I am a beginner in terms of knowledge of Access.

      Thank you for all the help.
      46941-146942-2

      Attachments:
    • #1593357 Reply

      RetiredGeek
      AskWoody MVP

      mrwowery,

      Welcome to the Lounge as a new poster! :cheers:

      A little more information would be useful.

      I assume there is a Network involved?
      Do the users save their workbooks on their local machines or a network location?
      How often do the users fill out the workbook?
      Are there multiple workbooks per user when you go to compile the data for your stats?

      Any other data you can provide on the process would be most helpful in making suggestions.

      There is a good possibility that you don’t need to move to Access but could accomplish your task with some Excel VBA coding.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1593358 Reply

      WSmrwowery
      AskWoody Lounger

      RetiredGeek,

      Thanks for your response.

      The users save the workbooks on a shared network drive in separate folders. The users will fill out the workbooks weekly. There are indeed multiple workbooks per user as that workbook is used as the master copy. The users will input their data and perform a save as. The name of the saved file also changes as they name based on date.

      By the end of the year, there will be approximately 3500 rows and 53 columns of data.

      I also have another process of manually grabbing data off of a SharePoint site that eventually is merged and compared with the above data. I would like to automate that process if possible.

      Thanks for your help.

    • #1593363 Reply

      RetiredGeek
      AskWoody MVP

      My suggestions would be to:

      Change the base file to a Template, this will keep the users from accidentally overwriting it with data.
      Create a Macro (VBA Code) that names the files with a standard template, e.g. UserName & Date.
      Use a macro to prevent the users from exiting the template manually, thus forcing the use of the naming macro.

      Code:
      Option Explicit
      
      ' Programmed by: RetiredGeek WSL
      ' Date         : 22 Jul 2005    Version: 1.0
      ' Updated      : 03 Sep 2014    Version: 2.0
      ' Copyright    : © 2004 Bruce E. Kriebel
       
      '*** Place this code in the ThisWorkbook Module
      
      Private Sub Workbook_BeforeClose(Cancel As Boolean)
      
         Dim bDispMsg        As Boolean
         Dim bExcelVer10Plus As Boolean
          
         bDispMsg = False
         
         With Application
         
             bExcelVer10Plus = IIf(Val(Application.Version) > 9, True, False)
      
             If bExcelVer10Plus And .CommandBars("Worksheet Menu Bar").Enabled Then
               bDispMsg = True
             Else
               If Not .CommandBars("Worksheet Menu Bar").Enabled Then
                 bDispMsg = True
               End If
             End If
             
             If bDispMsg Then
               MsgBox "Please use the Menu's to Exit the System" & vbCrLf & _
                      "Closing using the X will cause serious" & vbCrLf & _
                      "system problems!", vbOKOnly + vbCritical, _
                      "Error: Improper Attempt to Exit System"
               Cancel = True
             End If
             
         End With
         
      End Sub                  'Workbook_BeforeClose(Cancel As Boolean)
      

      Of course once you do this you need to give the user a way out. I use a custom menu to provide this functionality among others.

      Code:
      Option Explicit
      
      Public frmCarMenu As ufCarMenu
      
      '                         +-------------------------+             +----------+
      '-------------------------|       Auto_Open()       |-------------| 08/25/10 |
      '                         +-------------------------+             +----------+
      
      Sub Auto_Open()
      
         Application.ScreenUpdating = False
         ApplicationMenu
         Sheets("Avalon").Activate
         If CInt(Application.Version) > 11 Then SendKeys "%(x)", True
         
      End Sub                   'Auto_Open()
      
      '                         +-------------------------+             +----------+
      '-------------------------|     ApplicationMenu     |-------------| 02/18/16 |
      '                         +-------------------------+             +----------+
      'Called by: Auto_Open
      
      Sub ApplicationMenu()
      
         Dim myMenuBar As Object
         Dim newMenu   As Object
         Dim ctrl      As Object
         Dim oWkBk     As Workbook
      
         KillApplicationMenu "Vehicles Menu" 'Deletes menu bar if it exists before recreating
          
         Set oWkBk = ActiveWorkbook
      
        Set myMenuBar = _
            CommandBars.Add("Vehicles", msoBarLeft + msoBarTop, , True)
         myMenuBar.Visible = True
         Set newMenu = _
            myMenuBar.Controls.Add(Type:=msoControlPopup, temporary:=True)
         newMenu.Caption = "Vehicles Menu"
      
         If Not oWkBk.ReadOnly Then
           Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
           With ctrl
               .Caption = "&Add Entry"
               .TooltipText = "Add a new entry for this Vehicle"
               .Style = msoButtonCaption
               .OnAction = "Add_Entry"
           End With
         End If
         
         Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
         With ctrl
             .Caption = "&Print Current Sheet"
             .TooltipText = "Print the currently displayed Vehicle"
             .Style = msoButtonCaption
             .OnAction = "Print_It"
         End With
      
         Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
         With ctrl
             If ActiveWorkbook.ReadOnly Then
               .Caption = "&Quit"
             Else
               .Caption = "&Quit No Save"
             End If
            .TooltipText = "Quit Cars w/o saving data"
            .Style = msoButtonCaption
            .OnAction = "ExitQuit"
         End With
      
         Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
         With ctrl
             If ActiveWorkbook.ReadOnly Then
               .Caption = "&Exit"
             Else
               .Caption = "Save and &Exit"
             End If
            .TooltipText = "Exit Cars and save data"
            .Style = msoButtonCaption
            .OnAction = "ExitSave"
         End With
         
      End Sub                   'ApplicationMenu
      
      
      '                         +-------------------------+             +----------+
      '-------------------------|        ExitSave()       |-------------| 09/02/14 |
      '                         +-------------------------+             +----------+
      'Called by: Menu Item
      'Calls    : Exit_Program()
      'Functions:
      
      Sub ExitSave()
      
         Exit_Program True
         
      End Sub
      
      '                         +-------------------------+             +----------+
      '-------------------------|       ExitQuit()        |-------------| 09/02/14 |
      '                         +-------------------------+             +----------+
      'Called by: Menu Item
      'Calls    : Exit_Program()
      'Functions:
      
      Sub ExitQuit()
      
         Exit_Program False
         
      End Sub
      
      '                         +-------------------------+             +----------+
      '-------------------------|     Exit_Program()      |-------------| 09/02/14 |
      '                         +-------------------------+             +----------+
      'Called by: ExitSave()
      '           ExitQuit()
      'Calls    :
      'Functions:
      
      Sub Exit_Program(zSaveData As Boolean)
      
         Dim oSheet          As Object
         Dim bExcelVer10Plus As Boolean
         
         With Application
         
             .ScreenUpdating = False
             .DisplayAlerts = False
             Sheets("Fit").Select
             Set frmCarMenu = Nothing   '*** Destroy instance of form class ***
             
             KillApplicationMenu "Vehi&cles"
             
             On Error Resume Next
             
             .ErrorCheckingOptions.UnlockedFormulaCells = True
             Range("A2").Select
             
             For Each oSheet In ActiveWorkbook.Sheets
                oSheet.Select
                If ActiveSheet.AutoFilterMode Then _
                  ActiveSheet.AutoFilterMode = False
             Next oSheet
                    
             If Not ActiveWindow.DisplayZeros Then ActiveWindow.DisplayZeros = True
             
               If zSaveData And _
                  Not ActiveWorkbook.Saved And _
                  Not ActiveWorkbook.ReadOnly Then
                 ActiveWorkbook.Save
               End If
             
             bExcelVer10Plus = IIf(Val(Application.Version) > 9, True, False)
             If bExcelVer10Plus Then _
               .ErrorCheckingOptions.UnlockedFormulaCells = True
            
             .Quit
             
         End With  'Application
         
      End Sub                        'Exit_Program
      

      The above would create this menu:
      46943-ExcelStdMenu

      Of course you would modify the code for your needs but the important parts are setting up the menu in the Auto_Open and the exit routines for Exit with & without Save. In the save version is where you’d put the standard naming code.

      Of course then you can easily write a macro for your analysis workbook to loop through all the workbooks in your standard location to copy the Data sheet date into your master for analysis.

      Here’s some example code to do this:

      Code:
      Sub ImportSheets()
      
          Dim wkbImport   As Workbook
          Dim wkbThisBk   As Workbook
          Dim sht         As Worksheet
          Dim zFilename   As String
          Dim iFileCntr   As Integer
          
          Application.ScreenUpdating = False
          Application.DisplayAlerts = False
          
          Set wkbThisBk = ActiveWorkbook
      	zFilename = Dir("\MyBookLiveBEKDocsExcel*.xlsm") 'Assumes only your files in this directory
          
      	If zFilename = "" Then Exit Sub   'No files found!
      	
            Do 	
              Set wkbImport = Application.Workbooks.Open(Filename:=zFilename)
              Set sht = wkbImport.Sheets("Data")
              sht.Copy After:=wkbThisBk.Sheets("MasterFile")
              wkbImport.Close savechanges:=False
               
          End If
          
          Application.ScreenUpdating = True
          Application.DisplayAlerts = True
      
      End Sub   'ImportSheets()
      

      Note: The above code was adapted from code to import all sheets from selected files and is untested in its current form but should be structurally sound. And of course will require modification to fit your usage (data paths & sheet names).

      Post back w/any questions and/or comments.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      Attachments:
    • #1593372 Reply

      WSmrwowery
      AskWoody Lounger

      RetiredGeek,

      I really appreciate the help. What would be the pros to using this approach as opposed to setting up a database in MS Access? And would there be any possible issues that I may run into using Excel as a database?

      Thanks.

    • #1593380 Reply

      RetiredGeek
      AskWoody MVP

      mrwowery,

      Pros:

        [*]You don’t have to start from scratch and design a database and the attendant VBA code.
        [*]You didn’t specify whether or not you know Access but if you don’t you’d be better served sticking w/Excel as it appears you already have a handle on that.
        [*]You already have the data analysis portion written and debugged.

      Cons:

        [*]If you do decide to do access you’ll probably wind up exporting the data to Excel to do the analysis/graphing anyway, this will require VBA code.
        [*]Access is much trickier in a multi-user mode. You’re avoiding that in Excel by having the form filled out and then stored by the local machine.
        [*]You’ll have to design a fairly extensive data input form, which you’ve already done in Excel, and that will be much harder in Access as you have to use the Forms section of VBE and then write code to handle the code.
        [*]You’ll also have to retrain your users.

      I’m sure others could add to both lists.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1593385 Reply

        WSmrwowery
        AskWoody Lounger

        RetiredGeek,

        Once making the workbook containing the userforms and data a template file, do I write the VBA code within that file or the file I am attempting to create as the “master file”?

    • #1593389 Reply

      RetiredGeek
      AskWoody MVP

      mrwowery,

      The code for the menu & preventing use of the close box to exit Excel go in the template. The code to read all of the saved files goes in your analysis program.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Creating Access user interface from existing Excel VBA Framework

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.