File name to Cell
Thanks a lot very much.....
"Jacob Skaria" wrote:
Dear Ranjith
Try this
Sub test()
Dim intTemp As Integer
Dim arrData As Variant
Dim arrWorkBook As Variant
arrWorkBook = Array("AU.xls", "BU.xls", "DU.xls", "EF.xls")
arrData = Array("Austria", "Burma", "Du", "Ef")
For intTemp = 0 To UBound(arrWorkBook)
If Dir("C:\N\" & arrWorkBook(intTemp)) < "" Then
Workbooks.Open Filename:="C:\N\" & arrWorkBook(intTemp)
Range("A1") = arrData(intTemp)
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else
MsgBox "File missing : " & arrWorkBook(intTemp)
End If
Next
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"Ranjith Kurian" wrote:
Hey thanks a lot.
One more help i need, Suppose any of these file (AU.xls", "BU.xls",
"DU.xls", "EF.xls,) are missing it should pop a msg box stating this
particular file is missing if we click ok it should continue.
Is it possible to do..
"Jacob Skaria" wrote:
Try this untested code...arrData contains the text to be assigned to each
workbook
Sub test()
Dim intTemp As Integer
Dim arrData
Dim arrWorkBook As Variant
arrWorkBook = Array("AU.xls", "BU.xls", "DU.xls", "EF.xls")
arrData = Array("Austria", "Burma", "Du", "Ef")
For intTemp = 0 To UBound(arrWorkBook)
Workbooks.Open Filename:="C:\N\" & arrWorkBook(intTemp)
Range("A1") = arrData(intTemp)
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Next
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"Ranjith Kurian" wrote:
Hi
I have used the below code to open each workbook and format , now is it
possible to add a macro code so that it should add the workbook file name to
cell A2
example: if the file name is AU.xls then in same file in Cell A1 the name
should come as Austrila, and if the file name is BU.xls then in same file
Cell A1 the name should come as Burma etc..
Sub test()
Dim intTemp As Integer
Dim arrWorkBook As Variant
arrWorkBook = Array("AU.xls", "BU.xls", "DU.xls", "EF.xls")
For intTemp = 0 To UBound(arrWorkBook)
Workbooks.Open Filename:="C:\N\" & arrWorkBook(intTemp)
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Next
End Sub
|