Run macro without selecting again the path and file

[1Ronin]

Hello,


I have a macro that open a user form with few buttons and a has progress bar.
The macro is working like this:
- Button Browse = open Explorer to search for files to process (first open).
- In the same time is counting the files (to be used for progress bar)
- Button START = to run main macro; I need to open again Explorer for files search and select one (second open)
- macro continues to extract data from all files....

My problem is that I want to avoid second open of folder since is the same. When I press START I want to run immediately the macro, without Open window and select file.
I need a little help here...

See below the code:

[COLOR=#333333]'browse for short files[/COLOR]
[COLOR=#333333]Private Sub CommandButton6_Click()[/COLOR]
[COLOR=#333333]Dim Flink, Fname, Fadress As String, count As Integer[/COLOR]


[COLOR=#333333]Flink = Application.GetOpenFilename("All Files (*.*), *.*")[/COLOR]
[COLOR=#333333]If Flink = False Then Exit Sub[/COLOR]
[COLOR=#333333]TextBox1.Value = Flink[/COLOR]
[COLOR=#333333]'take the name of file to remove to path to have only directory[/COLOR]
[COLOR=#333333]Fname = Dir(Flink)[/COLOR]
[COLOR=#333333]Fadress = Left(Flink, Len(Flink) - Len(Fname))[/COLOR]
[COLOR=#333333]'MsgBox (Fadress)[/COLOR]
[COLOR=#333333]Fname = Dir(Fadress)[/COLOR]




[COLOR=#333333]'Dim FolderPath As String, path As String, count As Integer[/COLOR]
[COLOR=#333333]'FolderPath = "C:\Documents and Settings\Santosh\Desktop" 'Faddress[/COLOR]


[COLOR=#333333]'path = FolderPath & "\*.xls" 'Flink[/COLOR]


[COLOR=#333333]'Filename = Dir(path) 'Fname[/COLOR]


[COLOR=#333333]Do While Fname <> ""[/COLOR]
[COLOR=#333333]count = count + 1[/COLOR]
[COLOR=#333333]Fname = Dir()[/COLOR]
[COLOR=#333333]Loop[/COLOR]


[COLOR=#333333]'where to show no. of files[/COLOR]
[COLOR=#333333]Label5.Caption = count[/COLOR]
[COLOR=#333333]'Range("Q8").Value = count[/COLOR]
[COLOR=#333333]'MsgBox count & " : files found in folder"[/COLOR]


[COLOR=#333333]End Sub[/COLOR]




[COLOR=#333333]'extraction of short results and put in Data sheet[/COLOR]
[COLOR=#333333]'Private Sub Database_generateur()[/COLOR]
[COLOR=#333333]Private Sub CommandButton2_Click()[/COLOR]


[COLOR=#333333]'Time a section of VBA code using the Timer function[/COLOR]
[COLOR=#333333]Dim secs1 As Single[/COLOR]
[COLOR=#333333]Dim secs2 As Single[/COLOR]


[COLOR=#333333]secs1 = Timer()[/COLOR]


[COLOR=#333333]'tweak to speed up macro[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Application.DisplayStatusBar = False[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]Application.Calculation = xlCalculationManual[/COLOR]




[COLOR=#333333]Dim Flink, Fadress, Fname As String[/COLOR]
[COLOR=#333333]Dim data2 As Worksheet[/COLOR]
[COLOR=#333333]Dim indexrow, indexrow2 As Integer[/COLOR]
[COLOR=#333333]indexrow = Application.WorksheetFunction.CountA(ActiveSheet.Range("A:A")) + 1[/COLOR]
[COLOR=#333333]indexrow2 = 1[/COLOR]
[COLOR=#333333]indexrow = indexrow + 1[/COLOR]


[COLOR=#333333]Dim Fcount As Single[/COLOR]
[COLOR=#333333]Fcount = 0[/COLOR]




[COLOR=#333333]'declare active document[/COLOR]
[COLOR=#333333]Set data2 = ActiveWorkbook.Worksheets("data")[/COLOR]


[COLOR=#333333]'ask filename to load[/COLOR]
[COLOR=#333333]Flink = Application.GetOpenFilename("All Files (*.*), *.*")[/COLOR]
[COLOR=#333333]If Flink = "False" Then Exit Sub 'test if cancel[/COLOR]


[COLOR=#333333]'take the name of file to remove to path to have only directory[/COLOR]
[COLOR=#333333]Fname = Dir(Flink)[/COLOR]
[COLOR=#333333]Fadress = Left(Flink, Len(Flink) - Len(Fname))[/COLOR]
[COLOR=#333333]Fname = Dir(Fadress)[/COLOR]


[COLOR=#333333]Do While (Fname <> "") 'read all xls file[/COLOR]

[COLOR=#333333]'test column to fill[/COLOR]

I put only a part of code since rest is working well.

Thank you for help.

 

[JazzSP8]

Once you've got your path can you store it in a cell somewhere?

(I quite often hide things like this behind a graph, or change the font colour to the same colour as the cell (white font on a white background.))

That way you can get your second macro to pick it up from that cell.

 

[Norie]

Declare the variable for the path at the top of the userform module, then it can be used throughout the module.

 

[JazzSP8]

Declare the variable for the path at the top of the userform module, then it can be used throughout the module.

Ha. Didn't know that it retained them if you did that!

I do that to pass variables in a continuous sequence of code but always thought they got forgot!

Learn something new every day - Thanks

 

[1Ronin]

Declare the variable for the path at the top of the userform module, then it can be used throughout the module.

Can show me how to do it?
I'm beginner in VBA

 

[JazzSP8]

Declare your variables outside of the Sub, like this...

Dim Flink, Fname, Fadress As String, count As Integer

'browse for short files
Private Sub CommandButton6_Click()

Flink = Application.GetOpenFilename("All Files (*.*), *.*")
If Flink = False Then Exit Sub
TextBox1.Value = Flink

{etc...}

You'll notice that you'll get a line underneath the declarations, these are now Global and can be used by any Sub and will retain their values so when you want to use it in the second it'll already contain the path picked up by the first.

 

[1Ronin]

Thank you very much Jazz.
Is working fine now.

I also learn something new today.
Thanks all for support.

 

[JazzSP8]

Thank you very much Jazz.
Is working fine now.

I also learn something new today.
Thanks all for support.

Well, Norie deserves the credit really - Taught us both something!