VBA filename question (Excel 2003)

[MassSpecGuru]

Hello everybody!

I was wondering if I could get some help with some VBA code. I'm using Excel 2003 and am a complete novice to VBA, thus the request. For a work project, I'm trying to take some data from a laboratory instrument (as a .txt file), parse it, massage it, and sort it using Excel and generate an .xls file to upload the data to another company's website.

I can get the data sorted and parsed properly using general Excel worksheet functions; however, I want to automate as much of the process as possible using macros or VBA code. To this end, I've gone through some of the steps manually using macro recorder, modified it as much as I can, but I have a couple problems and/or questions (which I'll post separately).

My first problem is that when I open the text files of data, the recorded macro has the name of the template (spreadsheet) hardcoded into it. For example,

.
.
.
Selection.Copy
Windows( "Method #1 Template.xls").Activate
Sheets("Instrument Data").Select
.
.
.

One of the issues I foresee is that we run several diferent methods so eventually I'll have a named method template for each analysis (i.e. they will have completely different names, and not Method #1, Method #2, etc., but something like "Nitrate", "Sulfate", "Chloride", etc.) and I don't want to have go in and change the code for all 25 (so far) buttons I have inside so that the macros will run properly.

Is there a way to get the template filename automatically and have it (Excel) enter it where needed inside each macro? If it matters, the path can't be hardcoded either because the templates may be on different computers, hard drives, and in different locatons (i.e. folders) on the drive depending on instrument and user.

Thanks for your assistance!

 

[RoryA]

Does your macro open the text file? If so you should store a reference to the workbook at that point. Additionally, there is almost never a need to activate or select anything in code and it's far more efficient not to.
Without seeing more of the code, I can't really be more specific, I'm afraid.

 

[jim may]

Maybe you could engage an Inputbox()

 

[MassSpecGuru]

Does your macro open the text file? If so you should store a reference to the workbook at that point. Additionally, there is almost never a need to activate or select anything in code and it's far more efficient not to.
Without seeing more of the code, I can't really be more specific, I'm afraid.

Thanks for the quick response, Rory!

Yes, the macro opens the text file (that was kind of the point of trying to automate the process). Like I said, I know almost nothing about VBA, so in looking around the web I've found code for a function that seems to work. It opens a dialog box(?) and I can select which data file I want to work on.

Also, I'm sorry if "activating" or "selecting" isn't the recommended way to do things or is ineffecient, however, like I said before I'm new to VBA and that's what the macro recorder recorded. (Please excuse my ignorance, but efficiency reasons aside, if you don't activate or select, how can you copy data from one file to another without doing those two steps? :-/ )

Anyway, here is the code I have so far. I hope this helps answer your "question."


Sub OpenOneFile02()
Dim fn As Variant
fn = Application.GetOpenFilename("Instrument data,*.txt", _
1, "Select One 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").Select
Range("A1:Z600").Select
Selection.Copy
Windows("Method #1 Template.xls").Activate
Sheets("Instrument data 01").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Data summary").Select
Range("A1").Select
End Sub


Thanks

 

[MassSpecGuru]

Maybe you could engage an Inputbox()

Thanks for the suggestion, Jim. I'll look into the Inputbox() function.

 

[RoryA]

For example:

Sub OpenOneFile02()
    Dim fn As Variant
    Dim wbkThis As Workbook, wbkImport As Workbook
    Set wbkThis = ActiveWorkbook
    fn = Application.GetOpenFilename("Instrument data,*.txt", _
            1, "Select One File To Open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub
    ' the user didn't select a file
    Debug.Print "Selected file: " & fn
    Set wbkImport = Workbooks.Open(fn)
    With wbkImport.ActiveSheet
        .Columns("B:B").Insert Shift:=xlToRight
        .Range("A1:Z600").Copy Destination:=wbkThis.Sheets("Instrument data 01").Range("A1")
    End With
End Sub

 

[MassSpecGuru]

Thanks, Rory, this part seems to open the text file nicely.

However, after running this from one of the buttons in my worksheet, the display shows the text file (of instrument data) that was opened. I would like the display to return to the worksheet that has the button the code was launched from (you'll see why in another post).

Thus, I have two follow up questions regarding this code:

1. After pasting, can you close the original text file without saving any changes?
2. Can you have the display return to the original active sheet?

Thanks!

 

[RoryA]

Yes - add:

wbkImport.Close False

before the End Sub line.