0

1st. I'm trying to create a msoFolderpicker for users to select folder. the results get printed in a cell. I have this working I think. 2nd. I have another import (recorded) macro code that currently calls to a hardcoded folder path, and opens a specific txt file. By itself, it works. I would like to have the flexibility of the folder picker stored value so the import (recorded) marco code uses this for the path and all I need to hardcode is the filename.

My testing have included a limited knowledge of trying to store a value and then trying to recall that value within another macro code piece. I just don't understand how to connect two piece together. I have created a activex button and had is "Call" another macro. But can't get a call to work in this case.

    _______________________________
    Sub FolderPicker()
    '
    ' first attempt at a folderpicker. 
    ' Brings up file dialog box and user selects location path. Location path 
    is ' ' store in cell ("M11") on worksheet. This macro is stored on  
    'Sheets("Import_Macro").Select
    '
    '
   Dim diaFolder As FileDialog
    '
   Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
   diaFolder.AllowMultiSelect = False
   diaFolder.Show

   fle = diaFolder.SelectedItems(1)
   Range("M11") = fle

   Set diaFolder = Nothing

   End Sub
    _______________________________
   Sub import_parameter()
   '
   ' import_parameter Macro
    ' This was created with a recorded macro in excel.
    ' Opens a hardcode path and filename txt file and goes through the txt 
    ' delimited wizard
    ' then copies select data into the current open workbook. 
    ' This macro is stored on Sheets("App Settings").Select
    '
    Application.ScreenUpdating = False

    Workbooks.OpenText Filename:="C:\txtdata\cf_parameter.txt", Origin:=437, 
    _
      StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), 
    _
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), 
    TrailingMinusNumbers:=True
    Range("A2:G2000").Select
    Selection.Copy

    Windows("TRH Pre-Installation Guide.xlsm").Activate
    Sheets("App Settings").Select
    Range("B6:H6").Select
    ActiveSheet.Paste
    Selection.AutoFilter
    Application.CutCopyMode = False

    ActiveWindow.ActivateNext
    ActiveWindow.Close
    Range("B4").Select

    Application.ScreenUpdating = True

     End Sub
    _________________________________

Currently the import (recorded macro) code works by itself and opens the txt file and copies select data into workbook. However, i feel a better option is to have a changing folder path value instead of the hardcode folder path. Any help on this would be greatly appreciated.>!! I thank you in advance for your insight.

Boomer
  • 229
  • 1
  • 9
  • make `fle` public, or pass it as an argument to the function, `function fx(strPath as string)` then in `fx` you can use `strPath` – Nathan_Sav Feb 11 '19 at 14:57
  • Thank you for responding quickly --Nathan. I know nothing about vba or coding. But I'm trying to google how to's on what your guiding me towards. I don't understand the terminology yet. But do know what public means. I believe fle is the value storing the selected folder path. I'm so out of my depth I'm not sure what actually to ask. sorry. – Boomer Feb 11 '19 at 15:50
  • In reading, I'm wondering if I can place the word Public before the Dim statement and before the fle statement. Would this be what your describing? – Boomer Feb 11 '19 at 15:56
  • Did you google public varaibles in VBA? 2nd result should help https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba – Nathan_Sav Feb 11 '19 at 16:00
  • Hey Nathan, I have been reading and youtubing and have come up with a possible thought on how you would script this. So the Public keyword in front of anything didn't work and produced a lot of Invalid attribute in Sub or Function errors. So I then placed the ( Public fle As String ) line above the name ( Sub FolderPicker() ) line and I don't get the errors. I'm not sure of the terminology of what I'm describing but I think this way I'm placing the Public variable (to be used with any module inside the workbook. Is this the way you wanting me to think about it? Would this work? – Boomer Feb 11 '19 at 17:04
  • Maybe I'm placing the Public variable line outside the function (Sub beginning to End Sub). in trying to describe it. If moving forward with this, I can then use fle in my import recorded macro area and call to it some way. – Boomer Feb 11 '19 at 17:08
  • Ok, i can say a bit of forward progress I think. I am receiving an error that I believe is confirming that the Public fle value is being passed between the two modules. The folder path is coming through but the c:\ characters are not. My error is ((Run-time 1004 - Sorry, we couldn't find txtdatacf_parameter.txt. Is it possible it was moved or renamed or deleted? )) – Boomer Feb 11 '19 at 18:46
  • this is the Import (recorded macro) module call to open workbook and call the fle variable + the "cf_parameter.txt" filename. Workbooks.OpenText (Module33.fle + "cf_parameter.txt"), – Boomer Feb 11 '19 at 18:48
  • Also Nathan, the Module33 is the name of the FolderPicker modeule we are working with. – Boomer Feb 11 '19 at 18:49
  • Nathan, I just added a \ to the first line and tested. It pulled the text file as instructed. Workbooks.OpenText (Module33.fle + "\cf_parameter.txt") this import macro is working. – Boomer Feb 11 '19 at 19:04

0 Answers0