Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > File name to Cell
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default File name to Cell
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default File name to Cell
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)
With Rows("1:1").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Select Case Left$(activeworkbokk.Name, 2)

Case "AU": Cells(intTemp + 1, "A").Value = "Australia"
Case "BU": Cells(intTemp + 1, "A").Value = "Burma"
Case "DU": Cells(intTemp + 1, "A").Value = "Dubai"
Case "EF": Cells(intTemp + 1, "A").Value = "EF???"
End Select
Next
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ranjith Kurian" wrote in message
...
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default File name to Cell
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default File name to Cell
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default File name to Cell
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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
import file so that it is a actual csv file, no excel cell version broncoburt New Users to Excel 1 November 21st 09 09:09 PM
Macro to Open excel file when file name is entered in cell K[_2_] Excel Programming 4 March 17th 09 12:12 AM
Autoname a save as file from a cell in a read only file g4rod Excel Discussion (Misc queries) 2 October 6th 06 01:16 PM
Copying of cell text from one file to paste as cell comments in a 2nd file Wayne Excel Programming 4 March 2nd 05 05:05 AM
Saving a file(new) using the multiple cell contents as a file name Dave Peterson[_3_] Excel Programming 1 August 1st 03 01:40 PM


All times are GMT +1. The time now is 10:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement