Can the result of the import text array be captured to the clipboard and not to a new file?

[belinea2010]

The code below is the result of recording a macro while importing a tab delimited text file via the import wizard but this method has a major downside in that it creates a new workbook and sheet in which to place the imported data. The code shows that the imported data is loaded into an array before it is outputted to the new (unwanted) workbook.

If a user then selects that data in the new workbook and copies it to the clipboard it can then be pasted in to any workbook and sheet the user chooses.

So my question is - using VBA can the result of the array be intercepted (or captured to the clipboard?) just before it writes the results to a new file and instead pasted from the clipboard into the worksheet of my choice as set within the VBA code?

I have looked at Power Query but I really don't think that is the way to go (unless someone can say otherwise).

In my very unqualified mind I muse that an array is virtual in that while data is in the array it exists as memory somehow and when the array output is created the data is then written to where ever the code states so is there a reason that data cannot be placed in the clip board or even better simply written to the workbook & worksheet from where the macro was called.

I am probably very wrong hence my question?

Sub ImportTXT()
    Dim Answer As VbMsgBoxResult
    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
'Optional: FileDialog properties
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = "F:\"
    
'Optional: Add filters
    fDialog.Filters.Clear
    fDialog.Filters.Add "Text/CSV files", "*.txt"

    Answer = MsgBox("Are You Sure You Want To Import A Text File?", vbYesNo + vbCritical, "Import A Text File")
    If Answer = vbYes Then
        Application.ScreenUpdating = False

    FName = Application.GetOpenFilename()

' ImportTXT code copied from Macro recording
        Workbooks.OpenText FileName:=FName, Origin:=xlWindows, 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), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 9), Array(8 _
        , 9), Array(9, 9), Array(10, 1), Array(11, 9), Array(12, 9), Array(13, 9), Array(14, 9), _
        Array(15, 9), Array(16, 9), Array(17, 9), Array(18, 9), Array(19, 9), Array(20, 9), Array( _
        21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 9)), TrailingMinusNumbers _
        :=True
    
    End If
End Sub

 

[BobBridges]

It's an interesting question, one I've never had occasion to wonder about, and if you find out the answer I'd like to hear it myself. What I can offer is not an answer to the question you actually asked, but a couple of ways to get around your problem—I think.

1) Write the macro to allow the transfer into a worksheet; then collect the data from the worksheet and copy it to wherever you want it to go and discard the worksheet you didn't want.

2) This one is more work on the coding end but probably faster: Read the file yourself, using the FileSystemObject, and suck up the data into an array of your own making. Then pour the array into wherever you want to put it. No worksheet to delete afterward.

The second option is probably what I'd go for if the data is of any size. I do that sort of thing all the time. If you're not familiar with the FileSystemObject, I recommend you give it a try; you'll probably find uses for it elsewhere as well. I'll give you pointers if you're interested.

 

[Marc L]

Hi,​

​

wrong assertions in the initial explanation !​

​

importing a tab delimited text file via the import wizard but this method has a major downside in that it creates a new workbook and sheet in which to place the imported data.

This is far not the Import Wizard but the one who used the Macro Recorder choosed to open a text file as a workbook rather than to import it !​

​

The code shows that the imported data is loaded into an array before it is outputted to the new (unwanted) workbook.

You misread the code as it directly opens the text file as a workbook, no data in any array …​

Do not confuse an array as a fields parameter of the OpenText method and data in an array like you can easily check just reading the VBA help …​

​

If a user then selects that data in the new workbook and copies it to the clipboard it can then be pasted in to any workbook and sheet the user chooses.

So my question is - using VBA can the result of the array be intercepted (or captured to the clipboard?) just before it writes the results to a new file and instead pasted from the clipboard into the worksheet of my choice as set within the VBA code?

Totally useless as exist different methods to import directly a text file in a specific location !​

One is at beginner level as it's an Excel feature in the Data menu : Import from file …​

​

I have looked at Power Query but I really don't think that is the way to go (unless someone can say otherwise).

See in the Power section of this forum or the samples everywhere on the Web …​

​

In my very unqualified mind I muse that an array is virtual

No sense as an array can't be virtual …​

​

 

[belinea2010]

Hi Bob and thanks for your reply.

1) Write the macro to allow the transfer into a worksheet; then collect the data from the worksheet and copy it to wherever you want it to go and discard the worksheet you didn't want.

I initially tried this method and while it does work I wondered if there was a way to bypass the need for the transfer into a new worksheet and then having to copy back in to the worksheet from where the macro was called.

2) This one is more work on the coding end but probably faster: Read the file yourself, using the FileSystemObject, and suck up the data into an array of your own making. Then pour the array into wherever you want to put it. No worksheet to delete afterward.

I like this idea as this was the gist of what I was wondering.

The second option is probably what I'd go for if the data is of any size. I do that sort of thing all the time. If you're not familiar with the FileSystemObject, I recommend you give it a try; you'll probably find uses for it elsewhere as well. I'll give you pointers if you're interested.

Pointers would be very welcome Bob, thank you.

 

[Marc L]

Well read post #3 …​

​

 

[belinea2010]

​

​

​

This is far not the Import Wizard but the one who used the Macro Recorder choosed to open a text file as a workbook rather than to import it !​

​

​

I did not *Choose* to open a text file as a workbook rather than import it, when using the import text wizard there is no option to "Import it" in any other way than that which the import wizard is coded to do. If there was if would be helpful if you could explain rather than criticize!​

​

​

You misread the code as it directly opens the text file as a workbook, no data in any array …​

Do not confuse an array as a fields parameter of the OpenText method and data in an array like you can easily check just reading the VBA help …​

​

​

Listen friend, Until six weeks ago I have now knowledge of excel formulas and VBA and everything I have learned I have taught myself during the Covid 19 lockdown from reading online and asking questions in forums like this, in such a short time I believe I have done well. Considering this I think I can be forgiven if I misread the code and especially asking about arrays as an array of some kind is used in my code example. Again, do not criticize, be friendly and helpful, explain what you mean so that users of the forum make learn.​

​

Totally useless as exist different methods to import directly a text file in a specific location !​

One is at beginner level as it's an Excel feature in the Data menu : Import from file …​

​

In my original post, in the first paragraph, in fact in the first line I clearly state that I used the "Import from file" method to obtain the code but​

it does open the text file as a new sheet in a new work book with no option offered during the import wizard to change this behavior.​

​

Further I find the tone of your replies to be aggressive and dismissive, certainly not polite.​

​

If you have read all of my post you will see that I sat "In my very unqualified mind I muse that". I acknowledge that I am at the start of my Excel journey and "muse" is a state of thought, thinking out aloud and I posted my thoughts here hoping for a discussion not derision.​

​

​

See in the Power section of this forum or the samples everywhere on the Web …​

​

​

​

Ah yes the web, don't you think that I tried that, there are 1000's of web pages all offering solutions and who has time to try every single one?
Most of them are far to technical and do not give straight answers. I am mildly dyslexic and can only read so much before text on websites become garbled therefore I learn by practical lessons rather than reading text and reference books. Being given real working examples rather than being told to "go figure it out". If I could "figure" it out I would not be ask here?

No sense as an array can't be virtual …​

​

Again, rather than being so dismissive please offer an explanation!

While I absolutely appreciate all and any replies just look at the tone of yours compared to BobBridges

 

[Chihiro]

Just search for "FreeFile" in the forum search tool and you'll find plenty of examples on how to read contents of text file into string and/or into array.

Ex:

Dim fPath As String: fPath = "C:\Folder\SomeFile.txt" 'Or use file dialog to let fPath variable
Dim strContent As String
Dim intFF As Integer: intFF = FreeFile()

Open fPath For Input As #intFF
strContent = Input(LOF(intFF), intFF)
Close #intFF

Though you could directly split Input() into array using delimiter and/or line ending... I usually prefer to put it into string variable for further processing.

As text files generated will have different character at end of line... based on OS environment and other factors. You'll need to test yourself how exactly to split string into array.

Note: Windows based system usually puts CrLf (Carriage return and Line feed \r\n) as line ending, where as Unix/Linux based systems uses Lf (\n). In rare instances you may find line ending with Cr (\r). Usually as result of poor programming or mistake in config.

Your initial post is bit too broad for us to give any specific solution. As noted in "New Users - Please Start Here", it is always recommended to give specific sample file(s) and expected outcome to help us help you. Without it, you'll likely receive very broad and generic answer.

 

[Marc L]

I did not *Choose* to open a text file as a workbook rather than import it, when using the import text wizard there is no option to "Import it" in any other way than that which the import wizard is coded to do.

Activating the Macro Recorder and using the Excel feature 'From a file' - or "From a text file', depends on the version and language -​

on hundreds of computers with different Excel versions I never got such generated code using the OpenText method​

as this method not belongs to this feature !​

And when validating the real Import Wizard an input box appears to select the destination top left cell so no needs to use the clipboard …​

​

So retry and take screenshots of each step or better record a video of your manual operations as we wanna know how you did it.​

​

I clearly explained your confusion between a data array and the parameter array of the method opening a text file as a workbook​

like you can easily read in the VBA help of OpenText method, all is yet there …​

​

There is no tone but just facts …​

​

 

[BobBridges]

You still there, belinea, or have you given up on this place? Have you figured out how to read the file directly yet? I'm still here, if you want to work on it.

 

[belinea2010]

You still there, belinea, or have you given up on this place? Have you figured out how to read the file directly yet? I'm still here, if you want to work on it.

Hi Bob and thank you for asking.

I have given up on this forum as I do not need to be put down or belittled by the likes of Marc L who may well be experts in Excel but have no clue when it comes to people and social skills.

I came here under the assumption that this was a friendly place where people like me who are trying to learn can do so from experts like your good self in a friendly and hassle free atmosphere... sadly that has not been my experience.

 

[BobBridges]

Yeah, I understand. What you can do, if you want, is contact me at my Gmail account, robhbridges, and we'll work on it together. Easier via email anyway, although it benefits fewer people at a time.

...Although if you've followed that link I showed you to the documentation on the FileSystemObject, maybe by now you've figured it out and don't need any help.

 

[Marc L]

I have given up on this forum as I do not need to be put down or belittled by the likes of Marc L who may well be experts in Excel but have no clue when it comes to people and social skills.

When wrong facts are written it's normal anyone report it as you are not the only one on this forum​

and as your initial post may confuse many people !​

If you are not able to understand this then do not come to any forum …​

​

As now you know the Import Excel feature can directly load data in a destination worksheet​

without any useless operation like using the clipboard for example …​

​

Is it so difficult to redo your operations while recording them, whatever by video or by pictures ?!​

We wanna see how it could be possible …​

​