Delete data using Excel

Question

I get a 100 + excel table every month, from which I take a fixed range and paste it into another table to make a report.

I am trying to write a vba script to iterate my excel files and copy a range in one table, but I could not do it.

Is there an easy way to do this?

Answer 1

I wrote this many years ago, but maybe it will help you. I added an extension for the latest version of Excel (xlsx). Seems to work.

Sub MergeExcelDocs()
    Dim lastRow As Integer
    Dim docPath As String
    Dim baseCell As Excel.range
    Dim sysObj As Variant, folderObj As Variant, fileObj As Variant
    Application.ScreenUpdating = False
    docPath = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt,Excel Files (*.xls),*.xls,Excel 2007 Files (*.xlsx),*.xlsx", FilterIndex:=2, Title:="Choose any file")
    Workbooks.Add
    Set baseCell = range("A1")
    Set sysObj = CreateObject("scripting.filesystemobject")
    Set fileObj = sysObj.getFile(docPath)
    Set folderObj = fileObj.ParentFolder
    For Each fileObj In folderObj.Files
        Workbooks.Open Filename:=fileObj.path
        range(range("A1"), ActiveCell.SpecialCells(xlLastCell)).Copy
        lastRow = baseCell.SpecialCells(xlLastCell).row
        baseCell.Offset(lastRow, 0).PasteSpecial (xlPasteValues)
        baseCell.Copy
        ActiveWindow.Close SaveChanges:=False
    Next
End Sub

EDIT:

, . " ". ( , ). , . . , .

Answer 2

VBA, Excel :

Dim sourcePath As String
Dim curFile As String
Dim curWB As Excel.Workbook
Dim destWB As Excel.Workbook

Set destWB = ActiveWorkbook
sourcePath = "C:\files"

curFile = Dir(sourcePath & "\*.xls")
While curFile <> ""
    Set curWB = Workbooks.Open(sourcePath & "\" & curFile)

    curWB.Close
    curFile = Dir()
Wend 

, .

Answer 3

.

, , , , , / , . .

, , , , , INDIRECT().

:

=INDIRECT("'[C:\path\workbook.xls]MyWorksheet'!$A$2")

, .

, INDIRECT(). , , .

:

= INDIRECT("'[" & $A2 & "]MyWorksheet'!$" & ADDRESS(3, COL()))

, $A2 ( $ "2", ) MyWorksheet ( B2 , B3 ).

ADDRESS, .

, , , Excel $A2 COL() . .

, , ( 200). , VBA, Excel. , ODBC- ADO , , ( "" ODBC - "" " " ). , Excel, Excel.

Answer 4

Tools->Macro->Record New Macro 

,

Answer 5

,

, 100 , ? :)

, , . (@Mark Biek )

, ADO, . , - .

, , . , , , .

Answer 6

TransferSpreadsheet Access. . :

http://datapigtechnologies.com/blog/index.php/using-access-to-combine-multiple-excel-files-method-2/comment-page-1/#comment-1741

VBA.

Answer 7

VBA ().

(. 2):

Excel VBA

INDIRECT, Excel.

, excel, , . , , , . , Calculation Manual, Calculation to Automatic .

After that, if you just want to use values, you can use break links, or special values ​​to copy and paste.