PDA

View Full Version : Help Defining an ARRAY and c00 code



afzalw
07-04-2012, 07:03 PM
1. I want to define an array that variables should be the columns values of A1:A100 from a workbook that is placed in C:/A/B/C.xls inside sheet1.

Its actually the same workbook from where I am running this Macro.

I just started using Macro, 2 days ago so, I don't really no what should be the code but I guess it should be some thing like this BUT THIS DOES NOT WORK



vaFiles = Array( Thisworkbook.worksheet(1).value (A1:A3))



2. I got this code from SNB that gets names for all excel files inside all subfolders

Sub Extractexcellfilespathfromsubfoldersnb()
c00 = "D:\Afzal\*.xls"
sn = Application.Transpose(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & c00 & " /b /s").stdout.readall, vbCrLf))
Cells(1).Resize(UBound(sn)) = sn
End Sub

The small problem is that it does not work if I try to enter a path that is inside this first level subfolders.
e.g
It works fine for D:\Afzal\
but gives error when I try to put a 2nd level subfolder path like D:\Afzal\A\
thanks for help

nilem
07-04-2012, 07:58 PM
1. ...Its actually the same workbook from where I am running this Macro... In this case, try something like this
Sub tt()
Dim vaFiles
With Sheets("Sheet1")
vaFiles = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
End With
MsgBox UBound(vaFiles)
End Sub
2.
It works for me
c00 = "D:\Afzal\A\*.xls"

afzalw
07-04-2012, 10:03 PM
Thanks, for first part I was using the following code and it was working fine but, your code is better as I don't have to manually tell the range of the variable. But I got some errors using ur code.

I have defined vaFiles as a variable

This is mine code for the first part

vaFiles = Array(ThisWorkbook.Sheets(1).Range("A1").Value, ThisWorkbook.Sheets(1).Range("A2").Value,ThisWorkbook.Sheets(1).Range("A3").Value)

But you see here I have to write all cells name manually

So, I got this problem when I tried to ran your code


Sub tt()
Dim vaFiles
With Sheets("Sheet1")
vaFiles = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
End With MsgBox UBound(vaFiles)
End Sub

I used this statement in my code it worked fine but it give me Subscript our of range error with your code, even at the first loop i=1.

Set wbSource = Workbooks.Open(vaFiles(i))



2. For the 2nd Part I just found out it worked when there are no spaces in my folder name its quite odd though,
e.g it worked for D:/A/A/A/
but it wont work for D:/A/A A/A even though there is a folder with "A A" Name, So in short it doesn;t read spaces in folder names. Strange!!

nilem
07-04-2012, 10:23 PM
try
Dim vaFiles

With Sheets("Sheet1") 'or ThisWorkbook.Sheets(1)
vaFiles = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
End With
'In this case, vaFiles is a two-dimensional array, so
Set wbSource = Workbooks.Open(vaFiles(i, 1))
'for example
MsgBox vaFiles(2, 1)

' or
With Sheets("Sheet1") 'or ThisWorkbook.Sheets(1)
vaFiles = WorksheetFunction.Transpose(.Range("A1", .Cells(Rows.Count, 1).End(xlUp)))
End With
'In this case, vaFiles is a one-dimensional array, so
Set wbSource = Workbooks.Open(vaFiles(i))
'for example
MsgBox vaFiles(2)

Kenneth Hobs
07-05-2012, 06:57 AM
In your other thread, you asked for two parts. snb gave you the first part. For a thread, it is best to ask one question. In the next thread, add a link to the original thread.

A space character in the path or workbook name is not a problem for Workbooks.Open. I suspect that you have another issue. In this example, I open each xlsx and put the Sheet1's A1 value in column B next to the written filenames in column A.

Sub Test_FilenamesToCell()
Dim r As Range, c As Range, wb As Workbook

FilenamesToCell ThisWorkbook.path & "\*.xlsx", Range("A1")
Range("A:A").Columns.AutoFit

Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each c In r
With c
If .Value2 <> ThisWorkbook.FullName Then
Set wb = Workbooks.Open(.Value2)
.Offset(0, 1).Value = wb.Worksheets("Sheet1").Range("A1").Value
wb.Close
End If
End With
Next c
End Sub

Sub FilenamesToCell(path As String, cell As Range)
Dim sn As Variant
'path = "D:\Afzal\*.xls"
sn = Application.Transpose(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & path & " /b /s").stdout.readall, vbCrLf))
cell.Resize(UBound(sn)) = sn
End Sub

afzalw
07-05-2012, 12:18 PM
Thanks very much Nilem this code worked
Now I am using this code

With Sheets("Sheet1") 'or ThisWorkbook.Sheets(1)
vaFiles = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
End With

With

Set wbSource = Workbooks.Open(vaFiles(i, 1))

Kenneth I couldn't open path that have spaces in folder names. But thats not the big issue I can work out this manually.