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

 

[Jonmo1]

OK, should be
Set MyBook = Workbooks.Open(fn)


Apparently, it must be used with the parens when doing it in a Set variable = type of line.

 

[MassSpecGuru]

Thanks for the help, Jonmo1! For the most part your suggestions worked; I only had to make a small change to your code in order to get my macro to (almost) work the way I want it to (i.e. to close the instrument data file).

The only problem remaining is that I still get prompted as to whether or not I want to save the large amount of data on the clipboard. Is there any way to automatically answer "No" to that dialog box?

Thanks,
MSG


Sub OpenOneFile01()
Dim fn As Variant
Dim MyBook 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
Set MyBook = Workbooks.Open(fn)
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
Sheets("Data summary").Select
' Windows("Results_122209A.rdb.xls").Activate
' ActiveWindow.Close
MyBook.Close False
End Sub

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

 

[Jonmo1]

After your line that does the paste, put this line to clear the clipboard.

Application.CutCopyMode = False