Excel macro/VBA question

[MassSpecGuru]

I am designing a workbook that takes laboratory instrument data and after some minor manipulations, summarizes the results on a specific worksheet within the workbook. The overall process is fairly simple/straightforward (parsing, simple calculations, etc.) and I think I have what I need for the most part; however, I have a small problem I was wondering if somebody could help me with.

Since this is such a repetitive task, I thought I would try my hand at my first macro and placing some buttons in my worksheets. I have no problems following the code generated using Excel’s Macro Recorder, however there is a problem with the fact that the instrument data file name is not the same each time I want to process some data. Thus, I thought I’d also try my hand at adding a little bit of VBA code to the original macro code to make things work better.

The code (see below) all works fine except that now I cannot close the file the instrument data was copied from. I have tried two techniques to solve this issue, but have been unsuccessful so far and would like some input.

My first attempt (see Plan A below the “End Sub” code), was to call the data file explicitly and close it, however, when I substitute those two lines of code for the code between the ' ---------- remarks, I get a:

Run-time error ‘9’:
Subscript out of range

message and Excel kicks me into the Debugger, where apparently there is some problem with the

Windows(fn).Activate

command even though the Immediate window shows the correct file was chosen:

Selected file: C:\Validation Data\Instrument A\Results_122209A.rdb.txt

So, I tried resorting to the “tried-and-true” method of switching between open windows using the Alt-Tab keyboard trick. This is outlined in Plan B below.

This doesn’t work either. Instead, it closes the summary workbook -- but it does it without saving the changes, so that’s progress, right?

Can anybody suggest a way around this simple problem?

(Sorry for the brevity of this post, however, after typing my verbose message for almost an hour online, the original post disappeared and I am very frustrated to say the least!)

Thanks in advance,
MSG



Sub OpenOneFile01()
Dim fn As Variant
fn = Application.GetOpenFilename("Analyst data,*.txt", _
1, "Select A File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn
Workbooks.Open fn
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("A1:Z400").Select
Selection.Copy
Windows("Method Validation Template.xls").Activate
Sheets("Analyst data 01").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
' ----------
Windows("Results_122209A.rdb.xls").Activate
ActiveWindow.Close
' ----------
Sheets("Data summary").Select
Range("A1").Select
End Sub


Plan A:
Windows(fn).Activate
ActiveWindow.Close SaveChanges:=False

Plan B:
SendKeys "%{TAB}", True
ActiveWindow.Close SaveChanges:=False

 

[texasalynn]

Try this
Windows(fn).Activate
ActiveWorkbook.Close False

 

[MassSpecGuru]

Thanks for the quick response, Lynn, but it didn't work. The macro still stops at:

Windows(fn).Activate

and kicks me into debugger.

Besides, I don't want to close the entire workbook (I could already do that); however, I only want to close the "input" file, e.g. Results_122209A.rdb.txt.

Thanks.

 

[Jonmo1]

fn = Application.GetOpenFilename("Analyst data,*.txt", _
1, "Select A File To Open", , False)

GetOpenFilename returns the full path and filename c:\directory\filename
so fn =path\filename

Windows(path\filename) is not valid, that only wants the filename..

Once the book is opened, you don't refer to the path anymore..because it's already open and loaded into memory.

Try this

Set MyBook = Workbooks.Open fn
...rest of your code...
MyBook.Close False

 

[texasalynn]

sorry that's what I get for hurrying without testing

take a look at this
http://www.neuroinnovations.com/excel_vba_automation.html

 

[MassSpecGuru]

No problems, Lynn. I appreciate any and all responses. Someday I hope to be able to return the favor.

I do have another question, though, and I hope it's not too dumb:

Is there a difference between buttons created with View -> Toolbars -> Forms and View -> Toolbars -> Control Toolbox?

I created the buttons I am trying to run my code from via the Forms menu, not Control Toolbox.

BTW, I'm using Excel 2003. Sorry. I probably should have mentioned that in my original post.

MSG

 

[MassSpecGuru]

I'm not sure if it's something I did incorrectly, however, when I placed the "Set MyBook.Open fn line right after the "Debug.Print ..." line, I got a "Compile error: Expected: end of statement" message.

Thanks

GetOpenFilename returns the full path and filename c:\directory\filename
so fn =path\filename

Windows(path\filename) is not valid, that only wants the filename..

Once the book is opened, you don't refer to the path anymore..because it's already open and loaded into memory.

Try this

Set MyBook = Workbooks.Open fn
...rest of your code...
MyBook.Close False

 

[Jonmo1]

fn = Application.GetOpenFilename("Analyst data,*.txt", _
1, "Select A File To Open", , False)
Set MyBook = Workbooks.Open fn

 

[texasalynn]

I'm getting the compile error also?

 

[texasalynn]

ok I got it to work with JonMo1 code like this

Set MyBook = Workbooks.Open(fn)