Link to home
Start Free TrialLog in
Avatar of WS
WSFlag for United Arab Emirates

asked on

MS Access - VBA - Split the path of file and save in textbox

Hi ,

I have a form that have a field as "txtTarget" which save the destination path of file. I want to split the path and just save the require as For example: file path is (W:\GLO\ALL\...) , it should be save in table as (\GLO\ALL\EXMP1.pdf...) OR (\ABC.pdf), the drive name which is c:\ or D:\ should not be saved. In my code i have written code as
Me.txtTarget = Split(TargetFile, "\", 1)(0)

Open in new window

for splitting but it's not working as required.

Any help?

Also if anyone can also guide how to concatenate that back afterwards when require to open this file would be helpful.
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

You are taking the first element of the split (0 based) and should use the 2nd element based on your description. Try
Me.txtTarget = Split(TargetFile, "\", 1)(1)

Open in new window

SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WS

ASKER

@Anders, This give error message as "Scripting out of range"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WS

ASKER

@Norie, it works.

Can you guys me how to concatenate that back also? the user want to search for file afterwards so in that case , this target should concatenate with the server path for example as "\\189.30.20.1\GLO\..." , show how to do that?
Hi,

If you are sure your path always start with a letter, and has been validated, just strip the 1st and 2nd characters:
1:Me.txtTarget = right(TargetFile, len(TargetFile) - 2)

Open in new window


With UNC path, you might want to use something more elaborated:
Just use the & operator to concatenate strings
path = "\\189.30.20.1" & Me.txtTarget

Open in new window

Avatar of WS

ASKER

@Fabrice,

I'm trying the concatenate part from yesterday but i am not able to make that work, can you help me a bit in that. In the subform which is a datasheet in that on field link i put this code at event On Click but it's not working, any idea why?

Private Sub Link_Path_Click()
Dim path As String
path = "\\xxx.xx.xx.xx\" & Me.Link_Path
Application.FollowHyperlink Me.path

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WS

ASKER

I got it but this is also not working.

Private Sub Link_path_Click()

Dim path As String
path = "\\xxx.xx.xx.xx" & Me.Link_path

Application.FollowHyperlink path

End Sub

Open in new window

Are you sure that your path variable hold valid data ?
Avatar of WS

ASKER

yes there is the specific file at the path which is mention. i got the link when i copy file to this location and then i split the link into root and path for relative addressing , after that i want to concatenate it back to open that file.

Also note one thing the field link_path data type in hyperlink , does that create any problem?
Avatar of WS

ASKER

i found a problem while breaking it and i think this is the problem : path= "\\xxx.xx.xx.xx\#GLO\ALL\test.pdf# , so these "#" are not letting it opening this but i haven't add these "#" , from where these "#" signs are coming?? is this something due to data type or from where?
Use breakpoints,
Execute your code step by step (F8 hotkey),
Track your variables values in the spy window ect .... the usual debugging stuffs.
Avatar of WS

ASKER

Thank you Fabrice the code above started working when i change hyperlink field to text.

Thank you again.