CODEWITHSUNDEEP
vbaexcel
Meedee
Meedee

Reputation: 127

Copy data from a given filename/path vba

I'm trying to create an excel tool where it would extract data from a given filename(workbook). Let's say, on my main workbook in(Sheet1-Cell A1), users will enter the filename. Then on a cmdbutton click, it'll copy the data from that specific filename(workbook).

I have created a file that copies data from another workbook, however, it indicates the specific path & filename of the workbook where the data will be copied. 

Dim myData As Workbook
Set myData = Workbooks.Open("C:\Users\Desktop\Book2.xlsx")

Call Sample
Selection.Copy

What I want, is to allow users to just enter the filename, then excel will locate that file, select data from there & copy it on the main workbook(Sheet2).

Upvotes: 1

Views: 3876

Answers (1)

Meedee
Meedee

Reputation: 127

I figured something out

Sub copydata()

Dim path As String
path = InputBox("Please input path")

Application.ScreenUpdating = False

Dim actualfile As Workbook
Set actualfile = ActiveWorkbook

Dim script As Object
Set script = CreateObject("Scripting.FileSystemObject")

Dim catalogue As Object
Set catalogue = script.GetFolder(path)

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

Dim textfile As Object
For Each textfile In catalogue.Files

    Workbooks.Open textfile

    Dim loadedfile As Workbook
    Set loadedfile = ActiveWorkbook

    loadedfile.Worksheets(1).Range("A2").CurrentRegion.Offset(1, 0).Copy

    actualfile.Worksheets(1).Range("A2").Offset(1, 0).End(xlUp).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    loadedfile.Close Savechanges:=False

Next textfile

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

End Sub

The only problem though is, it copies data to the column after the heading instead of copying it to the row below the heading - help on this is very much appreciated! :)

Upvotes: 1

Related Questions