Excel Create Array Variable

B

Brian

Good afternoon - I have a stupid question for the VBA gurus:

I have some code that takes a file list of pdf documents and merges
the listed documents together. This works great, with the exception
that I have "hardcoded" the file list in the array variable.

What I would like to do is have the user select a list of pdf file
paths in Excel, then have some code that convert what is currently
selected into the array variable so that the rests of the code may
continue.

In other words, I currently have
myArray("c:\test1.pdf", "c:\test2.pdf", "c:\test3.pdf")

I can't figure out the code to take a selected list of files from
excel into this array. Say the file list is A1:A3 -
A1 - c:\test1.pdf
A2 - c:\test2.pdf
A3 - c:\test3.pdf
If the user selects these 3 cells, I'd like them to be able to click a
command button which will kick of the merge program, and pass the
contents of what is selected to myArray, as listed above.

Help??
Brian Limbert
 
T

Tom Ogilvy

If selection.Areas.count > 1 then
msgbox "No go, quitting . . . "
exit sub
End if

vArr = Application.Transpose(Selection.Value)
 
B

Brian

Chip - thanks for the speedy reply and quick solution. However, I'm
certain I'm missing something blatantly obvious. It works up to where I
try to evaluate the content of each value in the array -

As you prescribed I set this variable:
InsertFileArray = Application.Transpose(Selection.Value)

Then when I try to evaluate the first value in the array,
If vFile = InsertFileArray(0)
I receive a "Subscript out of range" error.

I've snipped a little piece of the code out. Do I have the array
declared incorrectly?

Thanks for the assistance,
Brian

Public Sub GetPDF()

Dim PDDocSource As Object
Dim PDDocTarget As Object
Dim PDDocInsertSource As Object
Dim PDDocInsertTarget As Object

Dim InsertFileArray As Variant
Dim vFile As Variant

TargetPath = "C:\Documents and Settings\blimbert\Desktop\"
TargetFile = "test.pdf"

If Selection.Areas.Count > 1 Then
MsgBox "Nothing selected."
Exit Sub
End If

InsertFileArray = Application.Transpose(Selection.Value)

Set PDDocInsertSource = CreateObject("AcroExch.PDDoc")
Set PDDocInsertTarget = CreateObject("AcroExch.PDDoc")

For Each vFile In InsertFileArray
If InsertFileArray(0) = vFile Then 'Get the first pdf file in
the directory
If PDDocInsertSource.Open(vFile) <> True Then
MsgBox "Unable to open the source PDF - " & vFile
End
End If
Else


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
B

Brian

OK - now I'm proving how much I don't know. I got this to work, by
changing the statement of

If InsertFileArray(0) = whatever

to

If InsertFileArray(1) = whatever

I thought 0 was always the first value in the array, instead of 1.

Please help the clueless?
Brian


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tom Ogilvy

Arrays picked up this way are always 1 based, so the first element is

InsertFileArray(1)
 
T

_TWC_Wingnut

Hi Brian,

I can't find a snipet of code I had that does this, but you'll find
that if you midify this:

Using the FileSearch object
This function uses the FileSearch object, which is not supported in
all versions of Excel. Unlike the previous version of the function,
this one returns the full path as well as the file name.

Also, be aware that some users have reported problems using the
FileSearch object.

Function NewestFile(Directory, FileSpec)
' Returns the full path and name of the most recent file in a
Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files
Dim NumFound As Long
NewestFile = ""
With Application.FileSearch
.NewSearch
.LookIn = Directory
.FileName = FileSpec
NumFound = .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderDescending)
If NumFound > 0 Then NewestFile = .FoundFiles(1)
End With
End Function

.... you should be able to build your array (with a bit of tinkering).

FileSpec should be something like "*.pdf"
 
B

Brian

Thanks for the help and explanation. Two more questions to conclude
this topic:
1) Is there a way to make it so that you could do this fill this array
from a select **non-contiguous** range? It appears the transpose
function only works on a contiguos range.

2) How would I go about using the same type of result for an access
query table or query?

Thanks for all of your help - it is truly appreciated.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tom Ogilvy

#1
for a non-contiguous range, you would need to loop through the cells and add
the values one at a time to each element of the range.

#2
A variant variable can be passed an array. So I believe you can assign a
record to a variant and it will be passed as an array.

this article shows how to do it with DAO
http://support.microsoft.com/default.aspx?scid=kb;en-us;q149254

rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
'Return the records to an array variable.
varrecords = rs.GetRows(x)


I assume you could do something similar with ADO.
 
Top