Textbox.text to .getfolder(Path)

[ozan efendi]

Hi,

makro2 is my folders name.

i want to replace the folders' name every time when i write a folder name (only folder name / the folders will be inside the same path) in a textbox on the sheet.

For example: If i write Nov-12 to textbox the red lighted part will be Nov-12, if I write Oct-12 to textbox the redlighted part will be Oct-12, and it goes on. With that name path name (filepath) will change when i change folder name in textbox.

My whole code are in module1 and i think i need a textbox in Activex controls? Can you help me with the codes and processes?

Regards,

Ozan.


Here:

<code style="margin: 0px; padding: 0px; font-style: inherit; ">Set file1 = fso.GetFolder("C:\Users\ozzy\Desktop\Makro2")</code></pre>
My whole codes are:

Option Explicit


Sub Button1_Click()
Dim wbStore, wbSource As Workbook, wsStore, wsSource As Worksheet, LastRow As Long
Dim FilePath As String, FileName, FullName As String
Dim blnOpened As Boolean
Dim iCount As Integer
Dim file1 As Folder
Dim fl1 As File
Dim fso As FileSystemObject
Set fso = CreateObject("scripting.filesystemobject")
Set file1 = fso.GetFolder("C:\Users\ozzy\Desktop\Sales\[COLOR=#ff0000]Makro2[/COLOR]")
Call ToggleEvents(False)
Set wsStore = ThisWorkbook.Sheets("Sheet1")
For Each fl1 In file1.Files
If iCount <> 2 Then
FullName = fl1.Path
Set wbSource = Workbooks.Open(FullName)
Set wsSource = wbSource.Sheets("Sheet1")
LastRow = wsStore.Cells.Find(what:="*", after:=wsStore.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
wsStore.Cells(LastRow, "A").Value = wsSource.Cells(1, "B").Value
wsStore.Cells(LastRow, "B").Value = wsSource.Cells(4, "B").Value
wsStore.Cells(LastRow, "C").Value = wsSource.Cells(7, "B").Value
wsStore.Cells(LastRow, "D").Value = wsSource.Cells(7, "E").Value
wsStore.Cells(LastRow, "E").Value = wsSource.Cells(10, "E").Value
wsStore.Cells(LastRow, "F").Value = wsSource.Cells(13, "E").Value
wbSource.Close savechanges:=True
End If
Next
Call ToggleEvents(True)
End Sub


Sub ToggleEvents(blnState As Boolean)
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub
Function WbOpen(wbName As String) As Boolean
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function


End Sub

 

[ozan efendi]

anybody to help?

 

[Norie]

Ozan

Why not add a userform with a textbox and a command button?

The user enters the folder name in the textbox and then clicks the command button to run the code.

 

[ozan efendi]

yes it can work too.

 

[Norie]

Ozan

You shouldn't cross post Textbox.text to .getfolder(filename)