Copy Tab Delimited text file into open workbook.

[bravia]

Hi all,

I'm very new to VBA, up to now I've mainly been creating macros within Excels tools. I should probably point out that I'm using Office 365 on a Mac (which has thrown up some issues before!)

So far I've have managed to;
- Open a .txt file thats path and name will always be the same (luckily!) and import to a new workbook
- Copy columns (A:J)
- Then I want to swap to the workbook that the macro was trigged from without specifying a path as this will be different almost every time
- Paste that data in sheet 'VW Import' in (A1)
- Close the workbook the data was copied from.

Sub ImportData()
'
' ImportData Macro
'
    Workbooks.OpenText FileName:= _
        "/Users/dan/Library/Group Containers/UBF8T346G9.Office/ExcelExport/VWExport", _
        Origin:=xlMacintosh, 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)), TrailingMinusNumbers:= _
        True
    Columns("A:J").Select
    Selection.Copy
    'This is where I need to call back to my open workbook I'd like the data pasted into.  The worksheet will already be open as the Macro will be activated.
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWindow.Close
End Sub

Any guidance is much appreciated, as I said I'm very new to this, so forgive any bizarre coding.

THank

 

[Joe4]

Before you open your Text file in code, capture your current macro workbook in a workbook object. Then you will easily be able to access it, i.e.

Dim owb as Workbook
Set owb = ActiveWorkbook

Then you can jump back to it at any time in your VBA code like this:

owb.Activate

 

[bravia]

Thanks Joe,

That works a treat, it's thrown up another couple of questions.

That pastes the data in Sheet1, I'd like to paste it in A1 'VW Import' are you able to give me that line?

Also, regarding closing the other workbook I'm being prompted if I'd like to save, and there's a large amount of data on the clipboard. Is there a way to almost no confirm any of this?

Thanks again

 

[Joe4]

After you select the workbook you want to go to, just tell it which sheet to go to:

owb.Activate
Sheets("VW Import").Activate

Regarding your workbook close, try this:

ActiveWorkbook.Close savechanges:=False

 

[bravia]

Perfect thanks for you help!

I found that adding

Application.CutCopyMode = False

Disabled the data copied to clipboard dialog box.

Thanks for all your help!

 

[Joe4]

You are welcome.