Macro That Automatically Updates Data Itself In Excel
If you need to have a database that automatically updates itself.
In fact, if you have a folder with different excel files, say folder Y (all the same form/template), and the first X cells of these files need to be extracted to another excel workbook (file Z). Every file represents 1 row in workbook Z. But this should be done automatically.
So if I copy a new excel file in folder Y, the first X of this new excel file should automatically (can be with a ‘button demand’) be copied into workbook Y.
Clear Content of selected range including formulas,dropdown and Cell value in excel
If you need to have a database that automatically updates itself.
In fact, if you have a folder with different excel files, say folder Y (all the same form/template), and the first X cells of these files need to be extracted to another excel workbook (file Z). Every file represents 1 row in workbook Z. But this should be done automatically.
So if I copy a new excel file in folder Y, the first X of this new excel file should automatically (can be with a ‘button demand’) be copied into workbook Y.
Option Explicit
Const cstFolder = "C:\Users\ATC0155\Documents\Toolbox\Excel\1208 20 selfupdate"
Const cstCols = 5
Sub DoUpdate()
Dim wks As Worksheet
Dim strFile As String
Dim appExcel As Excel.Application
Dim lngRow As Long
' Prepare the sheet to receive the data
Set wks = Sheet1
wks.Cells.ClearContents
' Run through each file in the folder
strFile = Dir(cstFolder & "\*.xls*")
lngRow = 0
Do While strFile <> ""
If strFile <> ActiveWorkbook.Name Then
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open cstFolder & "\" & strFile
lngRow = lngRow + 1
appExcel.Sheets(1).Range(appExcel.Sheets(1).Cells( 1, 1), appExcel.Sheets(1).Cells(1, cstCols)).Copy
wks.Cells(lngRow, 1).PasteSpecial Paste:=xlPasteValues
wks.Cells(lngRow, cstCols + 1).Value = strFile
appExcel.Workbooks.Close
Set appExcel = Nothing
End If
strFile = Dir()
Loop
' Tidy up
Set wks = Nothing
End Sub
Clear Content of selected range including formulas,dropdown and Cell value in excel
Sub clearcontent(ByVal rgstr As String)
'Clear Content of selected range including formulas,dropdown and Cell value
Range(rgstr).Select
Selection.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub