Jump to content
Daniza

Get the Pathway Excel

Recommended Posts

Daniza

Hello Guys, Can you help me tweak my vba code

I have 3 forms that will fill up my vlookup formula can you lead me how to fill up this in one click after item was selected on Dialog Box?

UserForm1.TextBox1.Value = D:\BOOTDRV\AlohaTS\RptExport\ProductMix07012018.csv ( I need to remove filename, just the pathway D:\BOOTDRV\AlohaTS\RptExport\ )

UserForm2.TextBox2.Value = ProductMix07012018.csv (Get the filename only without the pathway)

UserForm2.TextBox3.Value = ProductMix07012018 (Remove extension)

Thank You very much.

I'll be using this on our Inventory Report ^_^

Sub Calculate()

Dim objDialog As Object

 Set objDialog = Application.FileDialog(3)
With objDialog
    .InitialFileName = UserForm1.TextBox1.Value
    .AllowMultiSelect = False
    .Show
       
        If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        UserForm1.TextBox2.Value = Dir(.SelectedItems(1))
        UserForm1.TextBox3.Value = Replace(UserForm1.TextBox2.Text, ".csv", "")
        End If
End With
Set objDialog = Nothing
End Sub

 

Share this post


Link to post
Share on other sites
water

Function _PathSplit should do what you want.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Daniza

Hello @water, I think _PathSplit is for Autoit what if I used vba code? :sweating:

Share this post


Link to post
Share on other sites
water

I see. Maybe this article sheds some light onto the subject: https://stackoverflow.com/questions/1743328/how-to-extract-file-name-from-path


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Daniza
Posted (edited)

Thank You Again Sir @water, Data for my vlookup is now set ^_^
 

Sub CalculatePmix()

Dim filename As String


With Application.FileDialog(3)
    .AllowMultiSelect = False
    .Show
       
        If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        filename = .SelectedItems(1)
        UserForm1.TextBox2.Value = Dir(.SelectedItems(1))
        UserForm1.TextBox3.Value = Replace(UserForm1.TextBox2.Text, ".csv", "")
        UserForm1.TextBox4.Value = Left(filename, InStrRev(filename, "\"))
        Application.ScreenUpdating = False
        Workbooks.Open (filename)
        UserForm1.TextBox5.Value = Sheets(UserForm1.TextBox3.Value).Range("A5").Value
        Workbooks(UserForm1.TextBox2.Value).Close SaveChanges:=False
        Application.ScreenUpdating = True
        UserForm1.TextBox6.Value = Format(CDate(UserForm1.TextBox5.Text), "DD")
                
End If
End With

End Sub

 

 

clipimage.jpg

Edited by Daniza

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×