Share via

VBA to copy data from mutiple csv files to one worksheet

Anonymous
2019-07-18T20:12:44+00:00

Folks, 

I have raw instrumental csv files with a summary excel file in the same folder. New csv files will be produced every time the instrument runs. I'm trying to use macro to open specific csv file and copy the data in the first 4 columns to certain 4 columns in the excel file. I only need to copy up to 5 cvs files at a time. (eg. a.cvs A:D --> summary.xlsm A:D, b.cvs A:D --> summary.xlsm E:H, c.cvs A:D --> summary.xlsm I:L, etc). The data in the excel stays in the same worksheet, and new data overwrites the old data.

I made 5 macro buttons in the same sheet. Every time I import a csv data file, all other data will be removed. I'd like to keep all the data I have until new data overwrites. This is the code I have for a.csv, for others, I simply changed the destination columns.

Could anyone help me with this? Thanks a bunch!

Sub Import_A()

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("SummarySheet")

ws.UsedRange.Clear

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file...")

    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, _

        Destination:=ws.Range("A1"))

        .TextFileParseType = xlDelimited

        .TextFileCommaDelimiter = True

        .Refresh

    End With

Columns("A:A").Cut Destination:=Columns("A:A")

Columns("B:B").Cut Destination:=Columns("B:B")

Columns("C:C").Cut Destination:=Columns("C:C")

Columns("D:D").Cut Destination:=Columns("D:D")

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-07-19T16:17:39+00:00

    Here it is. PowerQuery is just a small part of these webinars as they are focused more on the data output from PowerQuery, but they will give you a good introduction to PQ and the other "Power" features

    I like to download the webinar replay recordings for future reference, and download the example workbooks.  If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that).

    These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price).

    Getting Started with “PowerQuery” / “Get & Transform” > Tables > PivotTables > PivotCharts > Dashboards ... WIKI:   https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620?tm=1537369871055****

    Like any Office tool, Excel has a lot of "hidden" features.  I've been learning some of them. One of the really good parts of these features is the automation that is built in to them. Some of these features have been around since 2007, but they have been rarely talked about.   I am going to provide links to free webinars, videos and articles talk about these features:

    PowerQuery (PowerPivot) > Tables > PivotTables > PivotCharts > Dashboards ... ****

    At the very end of the wiki there are links to free download PDF ebooks about these features.

    *********************************

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-07-19T15:21:58+00:00

    Thanks very much for the information! I'd love to have the link to those webinars.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-07-18T21:27:20+00:00

    Macros are "old school".

    .

    If you use PowerQuery you can automate the process of importing data. After you setup the initial import you can simply use the "refresh" feature to import new files.

    .

    Here are links to some articles about using PowerQuery

    Getting Started with Get and Transform****

    ! Get & Transform (what’s new in 2016)       2015 11 11https://www.accountingweb.co.uk/tech/excel/excel-2016-get-transform

    a question was being posted toExcel Zone Any Answersconcerning the best way to compare two lists in order to identify items that were in one list but not the other. “The inclusion of the Power Query tools as an integral part of Excel 2016 within the Get & Transform group of the Data ribbon tab not only extends how much of the data processing burden Excel can cope with, but also has the potential to change the way we work within Excel itself*. To take a simple example, complex lookups involving large numbers of separate formulae could be replaced by joining two Excel tables within a Workbook Query**.”*

    ! Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool           2015 03 20   Jon Acampora ****https://www.excelcampus.com/powerquery/power-query-overview/

    Learn how Power Query will help transform your data into a format that is ready for pivot tables, reports, and so much more. Skill level: Beginner

    ET PT PivotTables Power Query BI.docx

    @ Integrating Power Query technology in Excel 2016                2015 09 10

    https://blogs.office.com/2015/09/10/integrating-power-query-technology-in-excel-2016/

    Do you often import data into Excel? Do you ever need to shape your data before you analyze it and build your reports? We are excited to let you know that your get-data experience in Excel is about to change forever. In Excel 2016 we have integrated Power Query technology into the Data ribbon under the Get & Transform section. Power Query technology enhances the self-service business analytics experience in Excel by simplifying data discovery, access and collaboration.

    .

    If you are interested I can give you a link to several webinars that demonstrate using PowerQuery.

    .

    Was this answer helpful?

    0 comments