Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    10

    Unanswered: Problem VBA Code

    Hello,

    We have recently received an access database with a screen on there for uploading information to a text file. This process was not doing anything when the user clicked the button and after stepping through the code, I found the problem part. This code looks as follows:

    Do While strFileName = ""
    strFileName = strGovNo & "_" & strRebType & "_" & strBillDate & ".txt"
    dlgCommon.Filter = "Text Files (*.txt)|*.txt"
    dlgCommon.DialogTitle = "Save Rebate File"
    dlgCommon.FileName = strFileName
    dlgCommon.ShowSave

    strFileName = dlgCommon.FileName

    It is the dlgCommon part where it looks like the user is given the option of where to save the file through some sort of window that it does not like and I get the message:

    "Run-time error '429':

    Active X Component can't create object"

    The references look like the attached. Can someone tell me what is wrong please? If I comment out the dlgcommon lines, it runs fine but does not tell you where the file is going and I have to find it manually.

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I have not used the Common Dialog control before, but it looks like it should work. My suggestion, move the reference to the Common Dialog to the top of your references list and see if that makes a difference.

    You also might try unchecking it, exiting references, then go back into references and check it.

  3. #3
    Join Date
    May 2006
    Posts
    10
    Thanks for the reply, unfortunately, it doesn't make a difference though. Incidently when you uncheck the common reference and go back in, it is no longer in the list. To get it back, you need to browse to the windows32 folder.

    Any relevance?

    Googled it too and it seems this dialog box and activex controls in general don't work very well with access - any more ideas?

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    By the look of the Code, if strFileName contains nothing then a name is applied to the string variable. Then a File Save Dialog is opened and permits you to select a Path to save this file to. On the same hand, since it contains a File Type Filter, it also allows you to select an existing file name as if to permit a overwrite.

    Personaly, I prefer to use API dialogs and eliminate the need for the ActiveX Control all together. Never the less....I can't assist you with the current situation but instead perhaps suggest a different idea.

    Instead of using the ActiveX Common Dialog, consider using a API Save dialog window or use the dialog system provided in MS-Office (Need 'Microsoft Office 10.0 Object Library' set in References). For example:

    Code:
       ' Need 'Microsoft Office 10.0 Object Library' set in References
       ' for this to work.  Place the code into the OnClick event of a
       ' Command Button or place it into a Function procedure.
       On Error GoTo dlg_Error
       Dim dlg As Object
       Dim strFileName As String
       Dim vrtSelectedItem As Variant
       
       strFileName = strGovNo & "_" & strRebType & "_" & strBillDate & ".txt"
       ' 1 is the File Open Dialog       -  msoFileDialogOpen
       ' 2 is the File SaveAs Dialog     -  msoFileDialogSaveAs
       ' 3 is the File Browser (Picker)  -  msoFileDialogFilePicker
       ' 4 is the Folder Browser (Picker)-  msoFileDialogFolderPicker  **
        Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
        With dlg
          ' Set the File Dialog Window Title
          .Title = "Select Folder To Save " & strFileName & " To..."
          ' Clear any File Dialog Filters
          .Filters.Clear
          ' Add a New File Dialog Filters
          '.Filters.Add "Text", "*.txt", 1  'Filters Don't Apply to Folder Dialogs
          '.Filters.Add "All files", "*.*"
          ' Set the Name for the action button in our File Dialog
          .ButtonName = "Save Here"
          ' Turn OFF File multi Select. We don't need this now.
          .AllowMultiSelect = False
          ' Set the File Dialog window to show List
          .InitialView = msoFileDialogViewList
              ' All Available Views:
                    ' msoFileDialogViewDetails
                    ' msoFileDialogViewLargeIcons
                    ' msoFileDialogViewList
                    ' msoFileDialogViewPreview
                    ' msoFileDialogViewProperties
                    ' msoFileDialogViewSmallIcons
                    ' msoFileDialogViewThumbnail
                    ' msoFileDialogViewWebView
          ' Set folder in which the File Dialog will start from.
          .InitialFileName = CurDir
          ' If the Action button is selected and a Folder has indeed been
          ' selected then place the path & generated file name back into
        ' the strFileName variable.
          If .Show = -1 And Not IsNull(.SelectedItems) Then
             For Each vrtSelectedItem In .SelectedItems
               ' If you were dealing with a multi-select File Open dialog then
               ' you would need to handle this with an Array.
                strFileName = vrtSelectedItem & "\" & strFileName
             Next
          End If
        End With
     
    Exit_dlg_Error:
        Set dlg = Nothing   ' Free memory
        Exit Sub
        
    dlg_Error:
        MsgBox Err.Description
        Resume Exit_dlg_Error
    As you can see, this dialog is rather flexable. You will need to handle the red items in code as you see fit. Here the user simply selects the path to place the generated file name. If you want to have the user actally select a file name then change the Dialog mode to a File Browser (msoFileDialogFilePicker).

    If you want to use a Save dialog created from API functions then let me know and I'll post one. You should also be able to locate it via search in this Forum.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    May 2006
    Posts
    10
    Thanks very much for that - I am totally new to access vba, so I was hoping not to need to replace any code but I will have a play in a test environment with your code - only way to learn I suppose!!!!

    Thanks again

  6. #6
    Join Date
    Jan 2004
    Location
    Philippines
    Posts
    30

    Exclamation Example of Array to attach multi-selected files

    Dear CyberLynx. Your example and comments about the File Dialog built into MS Office is great. I actually used your example for my coding.

    In your example, you commented that if a multi-select file dialog is to be used then an array is necessary. I am just wondering if you can post an example of such an array.

    I am honestly trying to create an array, but I am still not successful since the past 2 weeks.

    Thank you for your time!
    pao_pao3

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    FYI Cyberlynx was last active on this community some 3 years ago
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2004
    Location
    Philippines
    Posts
    30
    Dear healdem. Thank you for the notice. I will try to search the other threads and hope to find the example I am looking for. Thanks again.
    pao_pao3

  9. #9
    Join Date
    Dec 2010
    Posts
    8
    Hello pao_pao3,

    If you are indeed using the code provided by CyberLynx and you are trying to utilize it as a Multi-Select FILE dialog window then you will find that it's actually quite easy to modify providing there is some understanding about arrays with VBA.

    I have taken the code provided by CyberLynx and modified it to do exactly what I think you want it to do. I have placed the code into a Function Procedure named MultiGetFilesDld. See the code listing below:

    I have also provided a sample db to show this in action.

    Code:
    Private Function MultiGetFileDlg(strFileName() As String, Optional StartLocation As String) As Integer
       ' Need 'Microsoft Office 10.0 Object Library' set in References
       ' for this to work.  Place the code into the OnClick event of a
       ' Command Button or place it into a Function procedure.
       On Error GoTo dlg_Error
       Dim dlg As Object
       Dim vrtSelectedItem As Variant
       
       ' x is declared as a Integer variable so as to be used
       ' as a counter for the number of files selected.
       Dim x As Integer
       ' Zero our counter variable
       x = 0
       
       ' The strFileName() string array parameter passed to this function
       ' is declared elsewhere, perhaps in a button OnClick event. for
       ' example:
       '               Dim strFileName() As String
       '
       ' then this function is called like this:
       '
       '               Result = MultiGetFileDlg(strFileName(), "C:\")
       '
       ' from the very same event.
          
       ' The StartLocation string parameter is optional and allows for
       ' you to supply the starting point (path) for the the dialog
       ' within the disk system. If nothing is supplied then the last
       ' disk path accessed will be displayed.
       If StartLocation = "" Then StartLocation = CurDir
       
       ' 1 is the File Open Dialog       -  msoFileDialogOpen
       ' 2 is the File SaveAs Dialog     -  msoFileDialogSaveAs
       ' 3 is the File Browser (Picker)  -  msoFileDialogFilePicker
       ' 4 is the Folder Browser (Picker)-  msoFileDialogFolderPicker  **
        Set dlg = Application.FileDialog(msoFileDialogFilePicker)
        With dlg
          ' Set the File Dialog Window Title
          .Title = "Please Select Your Desired Text File(s)"
          ' Clear any File Dialog Filters
          .Filters.Clear
          ' Add a New File Dialog Filters
          .Filters.Add "Text", "*.txt", 1  'Filters Don't Apply to Folder Dialogs
          '.Filters.Add "All files", "*.*"
          ' Set the Name for the action button in our File Dialog
          .ButtonName = "Select"
          ' Turn ON File multi Select.
          .AllowMultiSelect = True
          ' Set the File Dialog window to show List
          .InitialView = msoFileDialogViewList
              ' All Available Views:
                    ' msoFileDialogViewDetails
                    ' msoFileDialogViewLargeIcons
                    ' msoFileDialogViewList
                    ' msoFileDialogViewPreview
                    ' msoFileDialogViewProperties
                    ' msoFileDialogViewSmallIcons
                    ' msoFileDialogViewThumbnail
                    ' msoFileDialogViewWebView
          ' Set folder in which the File Dialog will start from.
          .InitialFileName = StartLocation
          ' If the Action button is selected and a File has indeed been
          ' selected then place the path & generated file name back into
          ' the strFileName variable.
          If .Show = -1 And Not IsNull(.SelectedItems) Then
             For Each vrtSelectedItem In .SelectedItems
                ' If you were dealing with a Single-select File Open dialog then
                ' you would not need to handle this with an Array.
                ' We use the ReDim Preserve statement so as to increment the array
                ' and to preserve data placed into previous array elements. This allows
                ' us to set the size of the array "on the fly" so to speak.
                ReDim Preserve strFileName(x)
                ' Place the found selected file name into the current strFileName() array
                ' element.
                strFileName(x) = vrtSelectedItem
                ' Increment our counter variable since it's determined that a file name
                ' has been placed into the Array.
                x = x + 1
             Next
          End If
        End With
        
        'Have the function return the number of Array elements filled (files selected).
        MultiGetFileDlg = x
        
    Exit_dlg_Error:
        Set dlg = Nothing   ' Free memory
        Exit Function
        
    dlg_Error:
        MsgBox Err.Description
        Resume Exit_dlg_Error
    
    End Function
    The function shown in the code listing above (MultiGetFilesDlg) is declared as a Private function which means it must go into the form you intend to use it. If you want to use this function globally (from any form) then place the function code into a Code Module and declare it as Public. For example:

    Public Function MultiGetFileDlg(strFileName() As String, Optional StartLocation As String) As Integer

    Hope this helps you.
    Attached Files Attached Files
    Last edited by TopKick; 12-20-10 at 17:00.

  10. #10
    Join Date
    Jan 2004
    Location
    Philippines
    Posts
    30

    Thumbs up Thanks for the example!

    Dear TopKick! Thank you very much for the example my friend. I have been struggling to write my own array code to get the results I want, but it seems that your example will help me greatly to get on the right track.

    I will let you know once I get the desired results with the help of your example.

    Maraming salamat (thanks a lot)!
    pao_pao3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •