Dim Win1 as Window, Variants

[Jaye7]

I am new at using variants and don't know how to do the following.

THIS IS A TEST SCRIPT ONLY.

I have my script open a file that I choose, I then want to capture that window name so that I can close it at the end or do other stuff to that window, obviously the filename changes each time and therefore I wouldn't know what it is each time.

My script would be Dim Win1 as window and once the file is open then it would set the window name as win1 so that I can activate the window at any given time and do stuff in the window.

Sub test()
 
[COLOR=purple][B]Dim Win1 As Window[/B][/COLOR]
'
 do all sorts of stuff here
' EXAMPLE FOR TEST PURPOSES ONLY
 
Dim filename As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer
 
    '   Set Drive letter
        ChDrive "C:\"
    '   Set to Specified Path\Folder
        ChDir "C:\"
    '   Set File Filter
        Filt = "All Files (*.*), *.*"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        filename = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        'Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
 
 
        Workbooks.OpenText filename:=filename, Origin:=xlMSDOS, StartRow _
        :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(9, 2), Array( _
        30, 1), Array(36, 1), Array(45, 1), Array(54, 1), Array(63, 1), Array(73, 1), Array(81, 1), _
        Array(96, 1), Array(111, 1)), TrailingMinusNumbers:=True    Rows("1:5").Select
    Selection.Delete Shift:=xlUp
    Columns("A:C").Select
    Selection.Copy
 
    [COLOR=purple][B]Set Win1 = ActiveWindow[/B][/COLOR]
 
    Windows("Book3").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
 
 
    [COLOR=purple][B]Windows(Win1).Close[/B][/COLOR]
End Sub

 

[shg]

Jaye,

Did you not see, or not understand, or not like my suggestion in post #7?

 

[Norie]

Jaye

You still don't need to activate or select anything.

Also, it would not be easier to activate/select - it could actually make things more complicated and slow things down.

Here's your original code without any activating or selecting.

Dim wbMaster As Workbook    ' the master workbook
Dim wbSource As Workbook    ' the text file
 
Dim filename As Variant
Dim Filt As String, Title As String
Dim FilterIndex As Integer, Response As Integer
 
    ' create a reference to master workbook, assuming it's the workbook the code is in
    Set wbMaster = ThisWorkbook
 
    '   Set Drive letter
    ChDrive "C:\"
 
    '   Set to Specified Path\Folder
    ChDir "C:\"
 
    '   Set File Filter
    Filt = "All Files (*.*), *.*"
 
    '   Set *.* to Default
    FilterIndex = 5
 
    '   Set Dialogue Box Caption
    Title = "Please select a different File"
 
    '   Get FileName
    filename = Application.GetOpenFilename(FileFilter:=Filt, _
                                           FilterIndex:=FilterIndex, Title:=Title)
 
    '   Exit if Dialogue box cancelled
    If filename = False Then
        Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
        Exit Sub
    End If
 
    '   Display Full Path & File Name
    'Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
 
    ' open text file
    Workbooks.OpenText filename:=filename, Origin:=xlMSDOS, StartRow:=1, _
            DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(9, 2), Array(30, 1), Array(36, 1), _
                                                             Array(45, 1), Array(54, 1), Array(63, 1), Array(73, 1), _
                                                             Array(81, 1), Array(96, 1), Array(111, 1)), _
                                                             TrailingMinusNumbers:=True
 
    ' create a reference to the text file
    Set wbSource = ActiveWorkbook
 
    ' delete rows 1-5 in text file
    wbSource.Worksheets(1).Rows("1:5").Delete Shift:=xlUp
 
    ' copy columns A-C in text file to destination workbook
    wbSource.Worksheets(1).Columns("A:C").Copy wbMaster.ActiveSheet.Range("A1")
 
    ' close text file
    wbSource.Close SaveChanges:=False

 

[Jaye7]

Thanks for your patience and help everyone.

I just finished testing SHG's script and once tweaked it works great.

Norie, I will test your script, as I am not proficient with VBA I find it easier selecting as I always get myself into a lot of trouble when I don't select, you tend to use what works best for your skill level wether things run slow or not, I have a workbook where the scripts take 2 minutes to complete whereas I am sure if I could use scripts better it would take 10 or 20 seconds, but it works so I just get over the time factor.

Sub test()
    Dim wkb As Workbook
 
 Dim filename As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer
 
    '   Set Drive letter
        ChDrive "C:\"
    '   Set to Specified Path\Folder
        ChDir "C:\"
    '   Set File Filter
        Filt = "All Files (*.*), *.*"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        filename = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        'Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
 
 
 Workbooks.OpenText filename:=filename, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(68, 2)), _
        TrailingMinusNumbers:=True
 
 
    Set wkb = ActiveWorkbook
 
 
  Selection.Delete Shift:=xlUp
    Columns("A:C").Select
    Selection.Copy
 
 
 
    Windows("Book4").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
 
    wkb.Close SaveChanges:=False
End Sub

 

[Norie]

I would maybe say I favour code that works.

Activating/selecting can actually cause problems.

All you need to do is make sure you reference things properly and that's about it.

You've already started doing that by using 'wbk' to reference the file you are opening.

That can now be used elsewhere in the code whenever you want to refer to that workbook/file.

 

[Jaye7]

Thanks Norie,

I have saved your example in my VBA scripts file so that the next time I decide to use selecting or activating I will refer to your code, it's all a learning curve and up until now I just haven't been able to use scripts properly without selecting or activating.

Again, thanks everyone.