Dim Win1 as Window, Variants

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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.

Code:
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
What's your reason for working with a window object variable (whose associated workbook changes when you activate other workbooks), rather than with a workbook object variable?
 
Last edited:

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
By far the easiest is to work with 1 window and 1 Excel application.

There, you can programmatically open several workbooks.

You do not need to activate or select windows, and in fact, you should not. It only slows down code and leads to screen flicker.

The Excel application has a window handle, use Application.Hwnd to know that (Long) number.
 

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
My suggestion is that you capture the window handle when the window is opened. Then you can refer to the window by its handle.

Note:


  • OpenText cannot be used as a function so I substituted Open
  • If you want to have more windows open, then set up an array of handles.


Code:
Sub test()
    Set win1 = Workbooks.Open(Filename:="xls.txt")
     MsgBox win1.Name
    win1.Close
End Sub
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060

ADVERTISEMENT

It opens a text file and converts it to excel, I don't want to save the workbook I just want to extract data etc... and then close the workbook.

I could save it and then delete it but would rather just open it, do the job and then close it.

I do go back and forth from my activeworkbook to the temp workbook though so I would activate the window several times before closing it.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Again, no need to activate and select.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

How about like this instead:

Code:
Sub test()
    Dim wkb         As Workbook
 
    ' ...
 
    Set wkb = Workbooks.OpenText( _
              filename:=filename, _
              Origin:=xlMSDOS, _
              StartRow:=1, _
              DataType:=xlFixedWidth, _
              TrailingMinusNumbers:=True, _
              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)))
 
   ' ...
 
    wkb.Close SaveChanges:=False
End Sub
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
Hi Tlowry

Your code will not work for what I want as I need a couple of columns converted to text etc... and there are too many arrays in my actual script, I provided a test script and therefore probably made you think that it was a simply file open script.

Wigi, I need to select specific values from areas within the opened file and copy them across to my actual workbook, go back to the other workbook and then copy some other areas etc.. and so on and so and therefore need to select the workbook/ window multiple times.

Code:
Workbooks.OpenText filename:=MOPRO1.TextBox3.Value, 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(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
        , 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
        23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array( _
        36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array( _
        49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array( _
        62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 2), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), Array(74, 1), Array( _
        75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), Array(87, 1), Array( _
        88, 1), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), Array(100, 1), Array( _
        101, 1), Array(102, 1), Array(103, 1), Array(104, 1), Array(105, 1), Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1), Array(110, 1), Array(111, 2), Array(112, 1), Array( _
        113, 1), Array(114, 1), Array(115, 1), Array(116, 1), Array(117, 1), Array(118, 1), Array(119, 1), Array(120, 1), Array(121, 1), Array(122, 1), Array(123, 1), Array(124, 1), Array( _
        125, 1), Array(126, 1), Array(127, 1), Array(128, 1), Array(129, 1), Array(130, 1), Array(131, 1), Array(132, 1), Array(133, 1), Array(134, 1), Array(135, 1), Array(136, 1), Array(137, 1), Array(138, 1), Array(139, 1), Array(140, 1), Array(141, 1), Array(142, 1), Array(143, 1), Array(144, 1), Array(145, 1), Array(146, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), Array(151, 1), Array(152, 1), Array(153, 1), Array(154, 1), Array(155, 1), Array(156, 1), Array(157, 1), Array(158, 1), Array(159, 1), Array(160, 1), Array(161, 1), Array(162, 1), Array(163, 1), Array(164, 1)), _
        TrailingMinusNumbers:=True
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Jaye

You do not need to activate/select to copy data from one workbook to another.

You also don't need that array unless you are changing any of the data types of the fields you are importing.

As far as I can see you are just using the default format - General.

Oops, need to look closer.:oops:

You are changing some data types but those are the only ones you need in the array, as shg has posted.
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
I appreciate your help everyone, maybe I should explain better what I am doing.

1 - First I open the file, then I am searching within the file for certain text, when found I then copy certain cells based on the found text to my Master workbook.

2 - then I search for other cells based on a different criteria and then copy those cells and other cells across to my master workbook.

3 - I then insert a column and run a formula to calculate differences between values and delete rows and columns based on the criteria, I then copy this info to my master workbook.

4 - EXAMPLE - I could do other various filtering, copying, calculating processes with the workbook and then once I have finally finished, I then close the temporary workbook.

Therefore I have had to go back and forth from my master workbook to temp workbook hyperthetically 50 times.

Therefore if I could store the temp workbooks name and activate it those 50 times back and forth between temp and master workbooks it would be easier.

When I use the macro recorder to open the file and do tasks it keeps using the Windows(".xls").Activate to go from the temp to the master.

I hope that this clears up what I am trying to do, I am not great with vba just hack scripts that are provided and record others.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top