Copy Tab Delimited text file into open workbook.

bravia

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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.

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
VBA Code:
Dim owb as Workbook
Set owb = ActiveWorkbook

Then you can jump back to it at any time in your VBA code like this:
VBA Code:
owb.Activate
 
Upvote 0
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
 
Upvote 0
After you select the workbook you want to go to, just tell it which sheet to go to:
VBA Code:
owb.Activate
Sheets("VW Import").Activate

Regarding your workbook close, try this:
VBA Code:
ActiveWorkbook.Close savechanges:=False
 
Upvote 0
Perfect thanks for you help!

I found that adding

VBA Code:
Application.CutCopyMode = False

Disabled the data copied to clipboard dialog box.

Thanks for all your help!
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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
Back
Top