Macro to pick data from desktop

[Shakti Bansal]

Hi,

Can someone help me with VBA code to:-

1. Pick excel files from specified location on desktop
2. paste sheet 1 on above files in active worksheet one below the another

 

[Marc L]

Hi !

Activate Macro recorder and operate manually :
you will get your own free code base !

Post this code here if any optimization is needed …
​

 

[Chihiro]

FYI - Putting files/folders in desktop isn't recommended, if you intend to use code etc. In many company, IT/Network admin provision Desktop to networked computers using logon script or other methods. Which may override your typical desktop location and gives limited permission level. This can add complexity to code or cause it to fail.

 

[Kenneth Hobson]

Worksheets are placed before or after another. I don't know what one below the other means.

You should probably come up with a unique sheet naming convention. Obviously, it will fail if they have passwords and you don't pass them on Open.

Sub Main()
  Dim a, e, ws As Worksheet
  a = aFileOpen(CreateObject("WScript.Shell").SpecialFolders("Desktop"), _
    "Desktop Files", "*.xls; *.xlsx; *.xlsm")
  If Not IsArray(a) Then Exit Sub
  For Each e In a
    Set ws = Workbooks.Open(e, , True).Worksheets(1)
    ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ws.Parent.Close False
  Next e
End Sub

Function aFileOpen(initialFilename As String, _
  Optional sDesc As String = "Excel (*.xlsx)", _
  Optional sFilter As String = "*.xlsx", _
  Optional tfMultiSelect As Boolean = True)
  Dim a, i As Long
  With Application.FileDialog(msoFileDialogOpen)
    .ButtonName = "&Open"
    .initialFilename = initialFilename
    .Filters.Clear
    .Filters.Add sDesc, sFilter, 1
    .Title = "File Open"
    .AllowMultiSelect = tfMultiSelect
    If .Show = -1 Then
      ReDim a(1 To .SelectedItems.Count)
      For i = 1 To .SelectedItems.Count
        a(i) = .SelectedItems(i)
        aFileOpen = a
      Next i
    End If
  End With
End Function

 

[Shakti Bansal]

Hi !

Activate Macro recorder and operate manually :
you will get your own free code base !

Post this code here if any optimization is needed …
​

Worksheets are placed before or after another. I don't know what one below the other means.

You should probably come up with a unique sheet naming convention. Obviously, it will fail if they have passwords and you don't pass them on Open.

Sub Main()
  Dim a, e, ws As Worksheet
  a = aFileOpen(CreateObject("WScript.Shell").SpecialFolders("Desktop"), _
    "Desktop Files", "*.xls; *.xlsx; *.xlsm")
  If Not IsArray(a) Then Exit Sub
  For Each e In a
    Set ws = Workbooks.Open(e, , True).Worksheets(1)
    ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ws.Parent.Close False
  Next e
End Sub

Function aFileOpen(initialFilename As String, _
  Optional sDesc As String = "Excel (*.xlsx)", _
  Optional sFilter As String = "*.xlsx", _
  Optional tfMultiSelect As Boolean = True)
  Dim a, i As Long
  With Application.FileDialog(msoFileDialogOpen)
    .ButtonName = "&Open"
    .initialFilename = initialFilename
    .Filters.Clear
    .Filters.Add sDesc, sFilter, 1
    .Title = "File Open"
    .AllowMultiSelect = tfMultiSelect
    If .Show = -1 Then
      ReDim a(1 To .SelectedItems.Count)
      For i = 1 To .SelectedItems.Count
        a(i) = .SelectedItems(i)
        aFileOpen = a
      Next i
    End If
  End With
End Function

Hi Marc,

I have a excel workbook named "consolidated data",in which there is a sheet with name "data".

Now I have to
1) Open excel files from a folder with name "Essbase" on desktop
2) Go to sheet "area" on these files,copy the data
3) paste data as per point 2 in tab "data" in workbook "Consolidated data".
4) Data pasted should be below the another,for example if first data ends in row 5,next data should be pasted from row 6.

 

[Kenneth Hobson]

If you want to just combine data from the first sheet from an existing folder of files, there is no need to present a dialog for file selection(s). Is it all files that are excel in that folder or just: xls, xlsx, xlsm, etc.?

When combining the data, will column A always have data? Most do but one never knows....

 

[Chihiro]

If you are just looking for a solution. Try the tool in link.
http://www.rondebruin.nl/win/addins/rdbmerge.htm