• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 72
  • Last Modified:

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.
0
WS
Asked:
WS
  • 7
  • 5
  • 2
  • +1
3 Solutions
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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

0
 
NorieVBA ExpertCommented:
Try splitting on ':' instead of '\'.

Me.txtTarget = Split(TargetFile, ":")(1)

Open in new window

2
 
WSStudentAuthor Commented:
@Anders, This give error message as "Scripting out of range"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
 Split("W:\GLO\ALL\EXMP1.pdf", ":", 2)(1)

Open in new window

0
 
WSStudentAuthor Commented:
@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?
0
 
Fabrice LambertFabrice LambertCommented:
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:
0
 
Fabrice LambertFabrice LambertCommented:
Just use the & operator to concatenate strings
path = "\\189.30.20.1" & Me.txtTarget

Open in new window

0
 
WSStudentAuthor Commented:
@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

0
 
Fabrice LambertFabrice LambertCommented:
You misunderstand the meaning of the "Me" keyword:
It is  a reference to the current object, and it should be used only to remove ambiguities, as it give an alternative access to the current object's methods and properties.

path is a local variable and for sure have nothing to do with properties.
So the following will be enough to achieve your goal:
Application.FollowHyperlink path

Open in new window

0
 
WSStudentAuthor Commented:
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

0
 
Fabrice LambertFabrice LambertCommented:
Are you sure that your path variable hold valid data ?
0
 
WSStudentAuthor Commented:
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?
0
 
WSStudentAuthor Commented:
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?
0
 
Fabrice LambertFabrice LambertCommented:
Use breakpoints,
Execute your code step by step (F8 hotkey),
Track your variables values in the spy window ect .... the usual debugging stuffs.
0
 
WSStudentAuthor Commented:
Thank you Fabrice the code above started working when i change hyperlink field to text.

Thank you again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now