Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by lfpm062010 View Post
    There is always more than one way to do the same thing. Learning something new is not a bad thing.

    To answer you question about the error. Do you have the macro "m1_Upload_Data" already build? If the macro is alread build, then I guess it will be harder to bebug without looking at your Access database.



    If you can get it working the other way, then it will be good too.
    Ok, well, I tried opening the click on in VBA and then I copied the macro I have directly into it.
    But I still got the same error saying the command couldn't find my original macro

    Original macro I was trying to run = m1
    Macro I pasted into command button = click1

    Error is telling me it can't find m1, even when I'm not calling on m1 any more

  2. #17
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Is this code similar to the code you are trying to execute?

    Code:
    Dim strTable As String
    Dim strFilePath As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
    
        .AllowMultiSelect = False
        .Title = "Locate a Excel file to Import"
        .ButtonName = "Import"
        .Filters.Clear
    '    .Filters.Add "Excel", "*.xlsx"
        .Filters.Add "Excel", "*.xls"
        .InitialFileName = "C:\Test"
        .InitialView = msoFileDialogViewThumbnail
            If .Show = 0 Then
            'There is a problem
            Exit Sub
            End If
        'Save the first file selected
        strFilePath = Trim(.SelectedItems(1))
    End With
    
    'strTable = "tbl1_PartPricingDataAggregation"
    strTable = "tblTest"
    
    'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True

    You need to place your code in an event handler like a click event for a Command Button. Here is an example for a Click event for a Command Button named Command0

    Code:
    Private Sub Command0_Click()
    
    Dim strTable As String
    Dim strFilePath As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
    
        .AllowMultiSelect = False
        .Title = "Locate a Excel file to Import"
        .ButtonName = "Import"
        .Filters.Clear
    '    .Filters.Add "Excel", "*.xlsx"
        .Filters.Add "Excel", "*.xls"
        .InitialFileName = "C:\Test"
        .InitialView = msoFileDialogViewThumbnail
            If .Show = 0 Then
            'There is a problem
            Exit Sub
            End If
        'Save the first file selected
        strFilePath = Trim(.SelectedItems(1))
    End With
    
    'strTable = "tbl1_PartPricingDataAggregation"
    strTable = "tblTest"
    
    'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True
    
    
    end Sub
    You can call a Public function from a click event too.

    Code:
    Call  fMy_Export

    In order for your function to work it needs to be in a Standard Module and the module cannot share the same name as the Public Function.

    Code:
    Public Function fMy_Export()
    
    Dim strTable As String
    Dim strFilePath As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
    
        .AllowMultiSelect = False
        .Title = "Locate a Excel file to Import"
        .ButtonName = "Import"
        .Filters.Clear
    '    .Filters.Add "Excel", "*.xlsx"
        .Filters.Add "Excel", "*.xls"
        .InitialFileName = "C:\Test"
        .InitialView = msoFileDialogViewThumbnail
            If .Show = 0 Then
            'There is a problem
            Exit Sub
            End If
        'Save the first file selected
        strFilePath = Trim(.SelectedItems(1))
    End With
    
    'strTable = "tbl1_PartPricingDataAggregation"
    strTable = "tblTest"
    
    'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True
    
    End Function

  3. #18
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Well, each time I update the expression in the On Click Input box, the expression in the box changes as well.
    So that's what I'd written on that attempt.

    I've now tried:
    On Click
    -m1_Upload_Data
    -m1_Upload_Data ()
    -"m1_Upload_Data"
    -"m1_Upload_Data ()"
    -RunApplication
    -DoCmd.RunMacro"all m1's above"
    -pasting my m1_Upload_Data code into the command
    -defining a new variable making my m1_Upload_Data macro a public function

  4. #19
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Well, that is very similar.
    This is what I have now:

    Code:
    Option Compare Database
    
    'Name macro
    Sub m1_Upload_Data()
    
    
    'Define strTable type: Access Table where data will be appended/added
    Dim strTable As String
    
    
    'Define strFilePath type: Path to Excel .xls that will be uploaded into Access
    Dim strFilePath As String
    
    
    'Opens browse computer application
    With Application.FileDialog(msoFileDialogFilePicker)
    
    
    'Select one file at a time
        .AllowMultiSelect = False
    
    
    'Browser Title
        .Title = "Locate a Excel file to Import"
    
    
    'Name what the button command will do: Imports Excel file to Access
        .ButtonName = "Import"
        
    'Clear previous selection
        .Filters.Clear
    
    
    'Browser only works with .xls files, add ".xls" to Excel file
        .Filters.Add "Excel", "*.xls"
        
    'Location Browser begins
        .InitialFileName = "C:\Test"
        
    'Define Browser vie: thumbnail
        .InitialView = msoFileDialogViewThumbnail
            
    'If nothing is selected, have error sign appear
            If .Show = 0 Then
            Exit Sub
            
    'If file is selcted, do following
            End If
    'Save the first file selected
        strFilePath = Trim(.SelectedItems(1))
    End With
    
    
    'define strTable as what table in Access
    strTable = "tbl1_PartPricingDataAggregation"
    
    
    'Do command, transfer spread sheet, it is an import (meaning adding of information), to which Access table, from which Excel file, does the Excel file have headers, spreadsheet name!Range
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strFilePath, True, "tbl1_Upload_Data!A1:I10000"
    
    
    'Other forms
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl1_PartPricingDataAggregation", "C:\Test\TestExport.xlsx", False
    'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, strTable, strFilePath, False, "A1:L1000"
    
    
    End Sub
    
    So for public vs private functions. 
    Does that mean that public is what stays true for the entire module? private is per macro? because I only have 1 macro. 
    
    Does
    I don't understand this part though
    In order for your function to work it needs to be in a Standard Module and the module cannot share the same name as the Public Function.

    So if I just make my macros public and call them that should work?

  5. #20
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do not recommend using a Textbox Control to fire code for an Export Procedure. Perhaps a Button is a better option. Also, in Access, a Macro is an Object. It is not a block of code. A Macro is a Named Saved Object. It is not a Function or a Sub Procedure.

  6. #21
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Now I'm just lost.

  7. #22
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Answer:

    1) Make sure Module has a different name from Sub Functions
    2) Code:
    Code:
    Private Sub Name of Button Command - ie Command1 
    
    Call Name of Module's Private Sub  
    
    End Sub
    
    
    Yay I'm learning sooo much!! Thanks everyone!

  8. #23
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    When you are in one module and want to call a function in another module, make sure the function you are calling is Public and not Private.

    Call Name of Module's Public Function

    Also, Sub Procurers are for supporting other procedures and function within the same Module(Class). Use a Public Function to offer access to code from the Public Interface (other Modules).

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find and Replace Macro
    By TrevorM in forum Macros
    Replies: 11
    Last Post: 12-28-2013, 06:11 PM
  2. Macro Command ImportExportText
    By RubyBosch in forum Import/Export Data
    Replies: 3
    Last Post: 12-19-2012, 12:58 PM
  3. Find and Replace Within a Macro
    By bmschaeffer in forum Access
    Replies: 1
    Last Post: 12-20-2011, 11:27 AM
  4. Replies: 0
    Last Post: 01-12-2011, 12:43 PM
  5. run a windodws command from macro
    By broecher in forum Programming
    Replies: 5
    Last Post: 09-12-2010, 04:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums