0

I'd like to import some .txt tables to PowerPivot without clicking "From Other Sources" --> "Text File", but rather by running a VBA macro.

The idea is that there are several .txt tables, say

C:\Table1.txt
C:\Table2.txt
C:\Table3.txt
etc.

and to create a user form or similar so that a user can select which tables he needs, then VBA creates a single appended table in PowerPivot of the ones selected. I'd know how to do that if not for two parts:

1) How to import a table to PowerPivot from an external source (C:\) using a VBA command?

2) How to "append" those tables into one through VBA such that they wouldn't appear as different tables in PowerPivot, but rather as one table with always the same name?

I can find tangent questions and information, but no working examples of how to automate importing tables from external .txt sources (or .csv or .accdb for that matter) into a single PowerPivot table like this...

Thank you very much!

Jerros
  • 11
  • 1
  • 8

1 Answers1

1

Power Query allows a GUI driven interface to do exactly what you want without VBA. You may want to consider that took instead, as it interfaces natively with Power Pivot and can be embedded in a hosted workbook, whereas the VBA solution could never work on SharePoint or Power BI.

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • If such specific behavior is already build-in and can be embedded in a workbook then it sounds like a perfect way to go - I must've overlooked it when playing around with the PowerPivot possibilities. I'll try to find this functionality then, thanks. : ) – Jerros Dec 04 '15 at 15:06