Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
Posts: 21
Default How to extract data from File Path

I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents

I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default How to extract data from File Path

Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DK" wrote in message oups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents

I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?

  #3   Report Post  
Posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
Posts: 21
Default How to extract data from File Path

That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents


I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.


How can I do this quickly?- Hide quoted text -


- Show quoted text -



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,290
Default How to extract data from File Path


Another approach...

Sub tested()
Dim str As String
Dim var As Variant
str = "I:\Account_Documents\ABC\ABCXYZ\GOV Documents"
'zero based array is returned by the Split function.
var = VBA.Split(str, "\", -1)
MsgBox var(2) & vbCr & var(3)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DK"
wrote in message I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents
I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default How to extract data from File Path

See Jim's reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DK" wrote in message oups.com...
That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents


I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.


How can I do this quickly?- Hide quoted text -


- Show quoted text -





  #6   Report Post  
Posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
Posts: 21
Default How to extract data from File Path

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.
On Jun 13, 1:41 pm, "Ron de Bruin" wrote:
See Jim's reply

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.


On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK


Why not use DataText to columns in the menubar


--


Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm


"DK" wrote in ooglegroups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents


I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.


How can I do this quickly?- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,290
Default How to extract data from File Path


For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DK"
wrote in message
Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.

  #8   Report Post  
Posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
Posts: 21
Default How to extract data from File Path

Hi Jim
Thanks for your help!!

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?

Thanks again!!
On Jun 13, 2:43 pm, "Jim Cone" wrote:
For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"DK" wrote in message

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.



  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,290
Default How to extract data from File Path


How can it extract the values and yet still give an error?
On what line does the error occur?
I suspect you have some short file paths and var(3) does not exist.
'--
Use Option Explicit at the top ot the module.
Declare all variables
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"DK"
wrote in message
Hi Jim
Thanks for your help!!

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?

Thanks again!!
On Jun 13, 2:43 pm, "Jim Cone" wrote:
For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"DK" wrote in message

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.



  #10   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default How to extract data from File Path

Hi DK,

As Jim states, your code doesn't declare all the variables.
Also, you are specifying 65536 rows in the range, which is more than the
35000 you stated earlier. The Split() function is causing problems with empty
cells. You might want to include a conditional, and specify the last row.

Try:

Option Explicit

'To add to Jim's suggestion:
'
'If you want to create a full directory path in separate columns then modify
Jim's code as follows..

Sub tested()
Dim str As String, sPath1 As String, sPath2 As String
Dim var As Variant
Dim rCell As Range, rngCol As Range
Dim lLastRow As Long, r As Long

str = InputBox("Enter the range to start extracting the path data from")
If str = "" Then Exit Sub '//user cancels
With ActiveSheet
lLastRow = .Cells(Rows.Count, .Range(str).Column).End(xlUp).Row
Set rngCol = .Range(str).EntireColumn
End With

With rngCol
For r = 2 To lLastRow
'zero based array is returned by the Split function.
If Not .Cells(r, 1) = "" Then
var = VBA.Split(.Cells(r).Text, "\", , vbTextCompare)

sPath1 = var(0) & "\" & var(1) & "\" & var(2)
sPath2 = sPath1 & "\" & var(3)

' .Cells(r, 1).Offset(0, 3).Value = var(2)
.Cells(r, 1).Offset(0, 3).Value = sPath1
' .Cells(r, 1).Offset(0, 4).Value = var(3)
.Cells(r, 1).Offset(0, 4).Value = sPath2
End If
Next
End With
End Sub

hth
Best regards,
Garry


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default How to extract data from File Path

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?


If Garry (GS) is right and you simply ran into empty the rows after your
data, you could modify your code like this...

Sub Macro2()
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
If Ubound(var) < 0 Then Exit For ' or perhaps Exit Sub
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

Rick

  #12   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default How to extract data from File Path

Hi Rick,

As long as there are no blanks between the first and last rows.., your
suggestion is nice and simple.

I considered the existence of blank cells/rows a possibility so wrote code
accordingly. It seems to work without error when tested with various contents
in the source range, nicely skipping over any empty cells.

Best regards,
Garry

"Rick Rothstein (MVP - VB)" wrote:

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?


If Garry (GS) is right and you simply ran into empty the rows after your
data, you could modify your code like this...

Sub Macro2()
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
If Ubound(var) < 0 Then Exit For ' or perhaps Exit Sub
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

Rick


  #13   Report Post  
Posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
Posts: 21
Default How to extract data from File Path

Hi Garry ! Hello Rick!
There are no blank rows in the data except at the end.

Secondly, I tried both the things.

Garry, when I tried your code, it is extracting the first three
folders in column g. I only want the third folder. Similarly for
column H.

I still got the error, subscript out of range and I found out what is
causing it.

Rick, I got the error with this code too.

There is one directory which does not have a sub directory and that is
where the macro stops.

Can you please assist in rectifying this?

On Jun 13, 5:16 pm, GS wrote:
Hi Rick,

As long as there are no blanks between the first and last rows.., your
suggestion is nice and simple.

I considered the existence of blank cells/rows a possibility so wrote code
accordingly. It seems to work without error when tested with various contents
in the source range, nicely skipping over any empty cells.

Best regards,
Garry

"Rick Rothstein (MVP - VB)" wrote:



I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub


It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?


If Garry (GS) is right and you simply ran into empty the rows after your
data, you could modify your code like this...


Sub Macro2()
Dim var As Variant
For Each rCell In Range("D2:D65536")
var = VBA.Split(rCell.Text, "\", -1)
If Ubound(var) < 0 Then Exit For ' or perhaps Exit Sub
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub


Rick- Hide quoted text -


- Show quoted text -



  #14   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default How to extract data from File Path

DK,
Comment out the lines I added to Jim's code and uncomment his lines. This
will give you the folder only.

As for the error you're getting, I suspect that not all the path data values
are structured the same. I'll play with this and post back, but I think if
you add Rick's suggestion to the If... statement using the AND operator it
might be what you need. For example:

If Not .Cells(r, 1) = "" And UBound(var) 2 Then

GS
---
  #15   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default How to extract data from File Path

Here ya go..
Sub tested3()
Dim str As String
Dim var As Variant
Dim rngCol As Range
Dim lLastRow As Long, r As Long

str = InputBox("Enter the range to start extracting the path data from")
If str = "" Then Exit Sub '//user cancels
With ActiveSheet
lLastRow = .Cells(Rows.Count, .Range(str).Column).End(xlUp).Row
Set rngCol = .Range(str).EntireColumn
End With

With rngCol
For r = 2 To lLastRow
'zero based array is returned by the Split function.
If Not .Cells(r, 1) = "" Then
var = VBA.Split(.Cells(r).Text, "\", , vbTextCompare)
If UBound(var) 2 Then
.Cells(r, 1).Offset(0, 3).Value = var(2)
.Cells(r, 1).Offset(0, 4).Value = var(3)
End If
End If
Next
End With
End Sub

Regards,
Garry


  #16   Report Post  
Posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
Posts: 21
Default How to extract data from File Path

Hello Garry,
This worked like a charm!! Thank you so much for your help!!

Thank you all for bailing me out!

Regards
DK
On Jun 13, 7:05 pm, GS wrote:
Here ya go..
Sub tested3()
Dim str As String
Dim var As Variant
Dim rngCol As Range
Dim lLastRow As Long, r As Long

str = InputBox("Enter the range to start extracting the path data from")
If str = "" Then Exit Sub '//user cancels
With ActiveSheet
lLastRow = .Cells(Rows.Count, .Range(str).Column).End(xlUp).Row
Set rngCol = .Range(str).EntireColumn
End With

With rngCol
For r = 2 To lLastRow
'zero based array is returned by the Split function.
If Not .Cells(r, 1) = "" Then
var = VBA.Split(.Cells(r).Text, "\", , vbTextCompare)
If UBound(var) 2 Then
.Cells(r, 1).Offset(0, 3).Value = var(2)
.Cells(r, 1).Offset(0, 4).Value = var(3)
End If
End If
Next
End With
End Sub

Regards,
Garry



  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default How to extract data from File Path

On Wed, 13 Jun 2007 10:16:01 -0700, DK wrote:

I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents

I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?


Here's yet another approach. It "works on" a range of Selected Cells, and puts
the "extracts" in the adjacent two columns:


===================================
Option Explicit
Sub Extract()
Dim c As Range
Dim oRegex As Object
Dim mcMatchCollection As Object

Set oRegex = CreateObject("VBScript.RegExp")
With oRegex
.Global = True
.IgnoreCase = True
.Pattern = "[\\]([^\\]*)"
End With

For Each c In Selection
With c
.Offset(0, 1).Clear
.Offset(0, 2).Clear
If oRegex.Test(.Text) = True Then
Set mcMatchCollection = oRegex.Execute(.Text)
.Offset(0, 1).Value = mcMatchCollection(1).SubMatches(0)
.Offset(0, 2).Value = mcMatchCollection(2).SubMatches(0)
End If
End With
Next c

End Sub
==================================
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract only file name from folder path string in Excel? JayKay Excel Worksheet Functions 1 March 20th 09 05:57 PM
Formula too long - new file path is shorter than old file path - Excel 2003 Greg J Excel Worksheet Functions 1 November 22nd 06 06:16 PM
Using Relative path for XML data file? Mark W. Robbins Charts and Charting in Excel 0 June 7th 06 06:41 AM
Extract Path From String Andibevan[_2_] Excel Programming 2 June 15th 05 07:04 PM
Extract full path given filename Kim J. Excel Programming 3 October 14th 04 04:38 AM


All times are GMT +1. The time now is 02:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"