Is this macro scenario possible using VBA?
 

Hi there,

I'm new to VBA. I'd like to be able to have a column of cells in excel
that houses a directory path in each cell (for instance, A1 =
c:\folders\folder1, A2 = c:\folders\folder2, ... and so on). Each
destination directory contains a certain number of JPEG files. I need
to create a macro that looks in each folder and then spits out the names
of JPEGs in each folder.

For instance, if there are two JPEGs in c:\folders\folder1 (photo1.jpeg
and photo2.jpeg), could you write a macro for the following to occur:

A1 = c:\folders\folder1
B1 = photo1.jpeg
C1 = photo2.jpeg

For c:\folders\folder2, there are 4 photos (apple.jpeg, orange.jpeg,
banana.jpeg and grape.jpeg).

A2 = c:\folders\folder2
B2 = apple.jpeg
C2 = orange.jpeg
D2 = banana.jpeg
E2 = grape.jpeg

Also, given that the column of data I may be using for the directory
paths is not "A," I'd like to make it user defined so that I can use
"QQ" or "Z" or whatever I need to.

Many thanks for any energy devoted to my question.

Kelly


*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

See if this one will work...


Sub GetJPEGS()
Dim lRow As Long
Dim sPath As String
Dim sFname As String
Dim LastRow As Long
Dim i As Long
Dim j As Long

If Cells(1, 1).Value = "" Then
Exit Sub
End If

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For j = 1 To LastRow

sPath = Cells(j, 1).Value

sPath = sPath & "\"

sFname = Dir(sPath & "*.jpg", vbNormal) ' change ext as needed

i = 1

Do Until sFname = vbNullString
Cells(j, "a").Offset(0, i).Value = sFname
sFname = Dir
i = i + 1
Loop

Next j

End Sub



Mark Ivey


"Kelly" wrote in message
...
Hi there,

I'm new to VBA. I'd like to be able to have a column of cells in excel
that houses a directory path in each cell (for instance, A1 =
c:\folders\folder1, A2 = c:\folders\folder2, ... and so on). Each
destination directory contains a certain number of JPEG files. I need
to create a macro that looks in each folder and then spits out the names
of JPEGs in each folder.

For instance, if there are two JPEGs in c:\folders\folder1 (photo1.jpeg
and photo2.jpeg), could you write a macro for the following to occur:

A1 = c:\folders\folder1
B1 = photo1.jpeg
C1 = photo2.jpeg

For c:\folders\folder2, there are 4 photos (apple.jpeg, orange.jpeg,
banana.jpeg and grape.jpeg).

A2 = c:\folders\folder2
B2 = apple.jpeg
C2 = orange.jpeg
D2 = banana.jpeg
E2 = grape.jpeg

Also, given that the column of data I may be using for the directory
paths is not "A," I'd like to make it user defined so that I can use
"QQ" or "Z" or whatever I need to.

Many thanks for any energy devoted to my question.

Kelly


*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Wow, Mark. It works like a charm. Thank you so much - I'm incredibly
appreciative. Lots of good karma coming your way!

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Glad I could help out..

Mark Ivey

"Kelly" wrote in message
...
Wow, Mark. It works like a charm. Thank you so much - I'm incredibly
appreciative. Lots of good karma coming your way!

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Aloha again Mark,

I'm having trouble changing the desired column I'd like to apply this
macro to. From what I can tell, I'm supposed to edit

LastRow = Cells(Rows.Count, "A")

So, the "A", correct? Or am I totally off? When I change it to "B" or
"AA", it doesn't seem to pick up on the values in this column. I know
I'm probably doing something incorrectly. Any advice would be greatly
appreciated.

Mahalo,

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

LastRow = Cells(Rows.Count, "A") won't get you anything but row 66536 if you
change to this...............

LastRow = Cells(Rows.Count, "A").Row
MsgBox "last row is" & LastRow

You must make a few more changes...........

Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox "last row number is" & LastRow

Change the "A" to "B" or "AA" should work OK

Or perhaps more better is this which uses the ActiveCell.Column instaed of a
hard-coded column?

Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
MsgBox "last row number is" & LastRow


Gord Dibben MS Excel MVP


On Fri, 15 Feb 2008 15:35:57 -0800, Kelly wrote:

Aloha again Mark,

I'm having trouble changing the desired column I'd like to apply this
macro to. From what I can tell, I'm supposed to edit

LastRow = Cells(Rows.Count, "A")

So, the "A", correct? Or am I totally off? When I change it to "B" or
"AA", it doesn't seem to pick up on the values in this column. I know
I'm probably doing something incorrectly. Any advice would be greatly
appreciated.

Mahalo,

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Thanks for your input, Gord.

I attempted to add the activecell column piece of code, but this does
not then generate the names of the files inside of my directories. It
generates the row number which is last in the column of data.

I need to be able to specify which column my directory paths are located
in and then have the macro spit out the names of the files inside the
folder and move on to the next folder and do the same. The macro Mark
contributed looks at data in only column A. I need to be able to apply
his macro to any column of data I choose.

Any additional advice would be much appreciated. Many thanks again for
your assistance.

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

In one of your earlier posts you told Mark it worked like a charm but needed
only to be able to change the "A" to another column.

Make sure to also change the "a" in this line if you change the "A" to "B" or
"AA"

Cells(j, "a").Offset(0, i).Value = sFname


Gord


On Fri, 15 Feb 2008 16:52:26 -0800, Kelly wrote:

Thanks for your input, Gord.

I attempted to add the activecell column piece of code, but this does
not then generate the names of the files inside of my directories. It
generates the row number which is last in the column of data.

I need to be able to specify which column my directory paths are located
in and then have the macro spit out the names of the files inside the
folder and move on to the next folder and do the same. The macro Mark
contributed looks at data in only column A. I need to be able to apply
his macro to any column of data I choose.

Any additional advice would be much appreciated. Many thanks again for
your assistance.

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

My apologies, Gord. I think my question was confusing.

So the macro does work like a charm, but only for data in Column A. I
had not tested to see if this macro worked on other columns of data
until today. I need to know exactly what areas of the code to edit so
that I can get the macro to run in column B or AB, etc.

Taking a look at the whole macro, my initial guess was that I needed to
edit the areas marked by asterisks if I wanted to apply this macro to
column B, for instance:

Sub GetJPEGS()
Dim lRow As Long
Dim sPath As String
Dim sFname As String
Dim LastRow As Long
Dim i As Long
Dim j As Long

If Cells(1, 1).Value = "" Then
Exit Sub
End If

**** LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For j = 1 To LastRow

sPath = Cells(j, 1).Value

sPath = sPath & "\"

sFname = Dir(sPath & "*.jpg", vbNormal) ' change ext as needed

i = 1

Do Until sFname = vbNullString
***** Cells(j, "b").Offset(0, i).Value = sFname
sFname = Dir
i = i + 1
Loop

Next j

End Sub


This does not work though. Nothing happens when you run the macro.

Any advice?




*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Kelly,

Not totally sure how many files you may have or your expected format, but
here are two possible solutions. One is as you requested (from the current
column). But I thought I would add in another possible solution if you were
interested (from current column to a new worksheet). See what you think...


Mark


Sub GetJPEGS_At_Current_Column_To_The_Same_Sheet()
Dim lRow As Long
Dim sPath As String
Dim sFname As String
Dim LastRow As Long
Dim i As Long
Dim j As Long
Dim myColumn As Long

myColumn = ActiveCell.Column

If Cells(1, myColumn).Value = "" Then
Exit Sub
End If

LastRow = Cells(Rows.Count, myColumn).End(xlUp).Row

For j = 1 To LastRow

sPath = Cells(j, myColumn).Value

sPath = sPath & "\"

sFname = Dir(sPath & "*.jpg", vbNormal) ' change ext as needed

i = 1

Do Until sFname = vbNullString
Cells(j, myColumn).Offset(0, i).Value = sFname
sFname = Dir
i = i + 1
Loop

Next j

End Sub





Sub GetJPEGS_At_Current_Column_To_A_New_Sheet()
Dim lRow As Long
Dim sPath As String
Dim sFname As String
Dim LastRow As Long
Dim i As Long
Dim j As Long
Dim mainSheet As String
Dim targetSheet As String
Dim myColumn As Long

mainSheet = ActiveSheet.Name

myColumn = ActiveCell.Column

Sheets.Add
ActiveSheet.Name = "JPEG Listing"
targetSheet = ActiveSheet.Name

Sheets(mainSheet).Select

If Cells(1, myColumn).Value = "" Then
Exit Sub
End If

LastRow = Cells(Rows.Count, myColumn).End(xlUp).Row

For j = 1 To LastRow
sPath = Cells(j, myColumn).Value
sPath = sPath & "\"
sFname = Dir(sPath & "*.jpg", vbNormal) ' change ext as needed

i = 1

Do Until sFname = vbNullString
Sheets(targetSheet).Cells(j, "a").Value = Left(sPath,
Len(sPath) - 1)
Sheets(targetSheet).Cells(j, "a").Offset(0, i).Value = sFname
sFname = Dir
i = i + 1
Loop
Next j
End Sub

Is this macro scenario possible using VBA?
 

Many, many thanks Mark. I can see a use for both of these macros in my
current project. My sincere gratitude for your assistance once again.

Best wishes,

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Glad to help out...

Mark

"Kelly" wrote in message
...
Many, many thanks Mark. I can see a use for both of these macros in my
current project. My sincere gratitude for your assistance once again.

Best wishes,

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

Is this macro scenario possible using VBA?
 

Aloha again,

I have an add-on question regarding this macro. Is it possible to also
ascertain the dimensions of the JPEGS? In an ideal world, it would
process like this:


A1 = C:\pathtofolder\foldername
A2 = photoname1.jpg
A3 = width dimension
A4 = height dimension
A5 = photoname2.jpg
A6 = width dimension
A7 = height dimension
etc...

I imagine that the dimension information can be extracted from the file
properties, no? If not, is there some other way of ascertaining the
dimensions?

I really appreciate any energy devoted to my inquiry.

Mahalo,

Kelly




*** Sent via Developersdex http://www.developersdex.com ***