dcsimg
Results 1 to 3 of 3

Thread: Select file to obtain data and copy into an existing workbook

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2019
    Posts
    1

    Select file to obtain data and copy into an existing workbook

    Hi

    I am trying to create an analysis tool, but need to periodically need to refresh the input data.

    Input data is 5 tabs. so on my analysis tool would like to be able to click a button that
    a) Asks which file I want to import the data from (i can the select from a flatfile of the the latest data)
    b) The data from the new data will be copied in and pasted on top of (and replace) my existing data so i can refresh pivot tables, run analytic summaries etc

    E.g. AnalysisSheet.xls houses my base data + analytics
    20190101_Data = my raw data

    Data_sheet1, -> Analysis_sheet 1
    Data _ sheet2 -> Analysis_sheet2 etc

    Struggling to find a way to make this all work

    Thanks

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,564

    Re: Select file to obtain data and copy into an existing workbook

    What parts are you having difficulty with?

    I have several workbooks that I use to do tasks like this. There a number of ways to make it work. You could use a Settings sheet that has things like the Input File Path and Name for each sheet tab, or you can use a User Form that asks for the file path and name. Once you have that, you can clear the data from the appropriate sheet tab and import the new file data into it.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,758

    Re: Select file to obtain data and copy into an existing workbook

    are you looking for something like
    Code:
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "c:\temp\myfile.txt"
        .Show
        filepath = .SelectedItems(1)
    End With
    if you multi select you can loop the selected items to an array
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width