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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.