Setting a workbook name as a variable...

[apeface76]

OK--

I have two workbooks. The first workbook contains information that is copied to the second workbook. I then wish to go back to the first workbook and close it (and then repeat this process with additional workbooks' contents being copied to the second workbook). Should be simple...


Windows("Workbook1").Activate
ActiveWorkbook.Close



HOWEVER-- there are the following restrictions:

*The name of the first workbook changes daily at random.

*The first workbook is a nonExcel document that opens via Import Wizard & is never saved while it's open.

*This process should run without user interference - i.e., no forms that require the name of workbook being typed in by the user.

Any ideas? Many thanks in advance.


*

[anilsolipuram]

Are all the workbooks in same directory.

so you want to copy all the workbook into single workbook.

It is not clear, can you explain in detail.

[apeface76]

Are all the workbooks in same directory.

so you want to copy all the workbook into single workbook.

It is not clear, can you explain in detail.

I have a random number of text files (between 3-7). All of these text files are in the same directory.

I want to open the first text file in Excel and, after formatting it, copy its contents into a pre-existing Excel file (which is already open & located in a different directory).

I then wish to close the text file without saving it, open the second text file in Excel, copy its contents into the pre-existing Excel file, and so forth.

The names of the text files change daily.

I hope this clarifies things.

[anilsolipuram]

Read through the comments

Sub Macro1()
Dim ar As Variant
Dim file_path As Variant
file_path = "c:\test\" 'this is the path where the text files exist
ar = Array("Diag000.txt", "Diag001.txt") ' this are the files from which we need to copy
Dim file_name As Variant
file_name = ActiveWorkbook.Name
Cells.Select
Cells.Delete
For i = 0 To UBound(ar)
'below code will open the text demlimit by space
Workbooks.OpenText Filename:=file_path & ar(i), Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
Range(ActiveSheet.UsedRange.Address).Select
Selection.Copy 'copy the text file format
While ActiveWorkbook.Name <> file_name
ActiveWindow.ActivateNext
Wend
Dim r As Range
Set r = ActiveSheet.UsedRange
temp = Split(r.Address, ":")
If UBound(temp) > 0 Then
Range("a" & Range(temp(1)).Offset(1, 0).Row).Select
Else
Range("a" & Range(r.Address).Row).Select
End If
ActiveSheet.Paste 'paste into the workbook
Application.CutCopyMode = False
While ActiveWorkbook.Name <> ar(i)
ActiveWindow.ActivateNext
Wend
ActiveWindow.Close 'close the text file
Next
End Sub

[apeface76]

First off, thanks!

A couple questions:

1) Are the items in the array required to have fixed names ("Diag000.txt", "Diag001.txt)? Or can they be as follows: ("Diag" & TDate & "000.txt", "Diag" & TDate & "001.txt").


2) Since we don't know how many text files there will be each day, is it possible to include all possible text file names within the array & then skip over the ones not found in the directory?


Please pardon my ignorance-- I've only been working in VBA for about a month.

Read through the comments

Sub Macro1()
Dim ar As Variant
Dim file_path As Variant
file_path = "c:\test\" 'this is the path where the text files exist
ar = Array("Diag000.txt", "Diag001.txt") ' this are the files from which we need to copy
Dim file_name As Variant
file_name = ActiveWorkbook.Name
Cells.Select
Cells.Delete
For i = 0 To UBound(ar)
'below code will open the text demlimit by space
Workbooks.OpenText Filename:=file_path & ar(i), Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
Range(ActiveSheet.UsedRange.Address).Select
Selection.Copy 'copy the text file format
While ActiveWorkbook.Name <> file_name
ActiveWindow.ActivateNext
Wend
Dim r As Range
Set r = ActiveSheet.UsedRange

temp = Split(r.Address, ":")
If UBound(temp) > 0 Then
Range("a" & Range(temp(1)).Offset(1, 0).Row).Select
Else
Range("a" & Range(r.Address).Row).Select
End If
ActiveSheet.Paste 'paste into the workbook
Application.CutCopyMode = False
While ActiveWorkbook.Name <> ar(i)
ActiveWindow.ActivateNext
Wend
ActiveWindow.Close 'close the text file
Next
End Sub

[anilsolipuram]

1) Are the items in the array required to have fixed names ("Diag000.txt", "Diag001.txt)? Or can they be as follows: ("Diag" & TDate & "000.txt", "Diag" & TDate & "001.txt").

You can use variable in array value, no array values are not fixed.



I used error trapping mechanism to skip files which donot exist.









Sub Macro1()
Dim ar As Variant
Dim file_path As Variant
file_path = "c:\test\" 'this is the path where the text files exist
ar = Array("Diag000.txt", "Diag001.txt") ' this are the files from which we need to copy
Dim file_name As Variant
file_name = ActiveWorkbook.Name
Cells.Select
Cells.Delete
For i = 0 To UBound(ar)
'below code will open the text demlimit by space
on error resume next
Workbooks.OpenText Filename:=file_path & ar(i), Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
if err.description<>"" then
err.clear
go to a:
else
Range(ActiveSheet.UsedRange.Address).Select
Selection.Copy 'copy the text file format
While ActiveWorkbook.Name <> file_name
ActiveWindow.ActivateNext
Wend
Dim r As Range
Set r = ActiveSheet.UsedRange

temp = Split(r.Address, ":")
If UBound(temp) > 0 Then
Range("a" & Range(temp(1)).Offset(1, 0).Row).Select
Else
Range("a" & Range(r.Address).Row).Select
End If
ActiveSheet.Paste 'paste into the workbook
Application.CutCopyMode = False
While ActiveWorkbook.Name <> ar(i)
ActiveWindow.ActivateNext
Wend
ActiveWindow.Close 'close the text file
end if
a:
Next
End Sub

[apeface76]

now we're cooking with gas! many thanks for your help!