Activating a workbook you just opened

[Dante_Blake]

This strip of code is designed to open all .raw delimited text files in a folder, delimit them, and save them in another folder in .xlsm format. The script runs in a blank workbook and opens a text file one at a time in another workbook.

It gets as far as opening the first .raw file in the folder and then immediately failing. I am assuming that I am not properly activating the workbook I just opened.

Note that I cut some of the code out that isn't relevant to this question.

Sub Consolidate()

Dim Coll_Docs As New Collection
Dim Search_path, Search_Filter, Search_Fullname As String
Dim DocName As String
Dim i As Long
Search_path = "C:\Users\nathaniel\Desktop\Compression Data"
Search_Filter = "*.raw"
Set Coll_Docs = Nothing
DocName = Dir(Search_path & "\" & Search_Filter)
Do Until DocName = ""
Coll_Docs.Add Item:=DocName
DocName = Dir
Loop
For i = Coll_Docs.Count To 1 Step -1
    Search_Fullname = Search_path & "\" & Coll_Docs(i)
    Application.Workbooks.Open (Search_Fullname)
    Workbooks(Coll_Docs(i)).Activate
    ***Fails after workbook is open***

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

ActiveSheet.Name = Material & Temperature
ActiveWorkbook.SaveAs "C:\Data\" & Material & " " & Temperature & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Next

End Sub

If my assumption is correct, how do I properly activate the workbook?

Thanks!

[Andrew-R]

Is it failing when it opens the workbook or when you try to activate it?

If you put a break-point in the code and step through which line generates the error?

[tlafferty]

I'd suggest modifying this line:

Columns("A:A").Select

Change to:

Sheets(1).Columns("A:A").Select

My assumption is that Excel is looking for an object for the columns reference, in this case, a specified sheet.

[Dante_Blake]

That change got me past that line. However, I am having the problem that every cell, column, etc... command wants sheets(1). before it.

Is there a way to activate the sheet so that it assumes all commands are performed on that sheet without having to name it? Or is that not possible since I am running the code from a different workbook.

Thanks!

[Andrew-R]

What you could try:

1. Dim a workbook variable at the start of your procedure:

Dim wbkCurrentBook As WorkBook

2. Modify your open line to be:

Set wbkCurrentBook=Workbooks.Open (Search_Fullname)

3. Change your activate line to:

wbkCurrentBook.Activate

4. See if it works.

[Dante_Blake]

Thanks for your suggestion but I was still getting errors. I went ahead and added sheets(1). to everything. I figure that's a good habit to get into anyway. The code runs fine, cycles through everything!

Thanks again!