Get filename from .txt so I can close it later?

[trillium]

Hi

I have code that will open a text file (don't know the name it will be but will be the only one), but I need to figure out how to get that file name so that I can use it to close the file later on.

    Workbooks.opentext Filename:="*.txt", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
        , Array(3, 1)), TrailingMinusNumbers:=True
 
   Range("A2:C9").Select
    Selection.Copy

Windows("Current.xlsm").Activate
Sheets("Current Work").Select
Range("A1").Select
ActiveSheet.paste

After I copy the data over to the Current.xlsm file, I want to go BACK to that text file and close it, but I can't figure out how without somehow extracting the name of the file while doing that opentext function...

thoughts??

 

[Norie]

Why do you need the filename?

    Workbooks.OpenText Filename:="*.txt", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
        , Array(3, 1)), TrailingMinusNumbers:=True

    ' create reference to text file just opened
    Set wbTxt = ActiveWorkbook

    wbTxt.Sheets(1).Range("A2:C9").Copy Workbooks("Current.xlsm").Sheets("Current Work").Range("A1")

    wbTxt.Close SaveChanges:=False

 

[Jon Peltier]

1. You can streamline your code:

ActiveSheet.Range("A2:C9").Copy Workbooks("Current.xlsm").Worksheets("Current Work").Range("A1")
ActiveWorkbook.Close False

or

Workbooks("Current.xlsm").Worksheets("Current Work").Range("A1:C8").Value = ActiveSheet.Range("A2:C9").Value 
ActiveWorkbook.Close False

The first line in either block copies the range to the destination without selecting anything, the second closes the text file you just opened.

(You don't need to do all the selecting that the macro recorder recorded).

2. When the text file is opened, you can use ActiveWorkbook to get the file name:

Dim sTextFileName As String
Workbooks.OpenText blah, blah
sTextFileName = ActiveWorkbook.Name

You don't need to activate the text file to close it either, just:

Workbooks(sTextFileName).Close False

 

[trillium]

Hi Norie, thanks for the quick reply. Need to close the text file (that has the source data in it) and don't know how without calling it's name first? The users are very simple Admin people, so if the text file stays open, it will confuse them when they go to generate it again.... trying to make thier life as simple as possible!

 

[trillium]

Hi Jon!

I am copying the source data from the text file into an Excel file called Current.xlsm (As it is the work list that they will be doing that day). Would that code know to flip files?

I will try your option 2!!

Thank you also for the code shortcuts! Will endeavour to employ!

 

[Norie]

Both Jon and I have posted code that will close the opened text file, without using the file's name.

 

[trillium]

SOLVED: Get filename from .txt so I can close it later?

Thank you very much! Worked like a charm and I am learning more about working with vba!