Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Sub CombineSheetsFromAllFilesInADirectory() Dim Path As String 'string variable to hold the path to look through Dim FileName As String 'temporary filename string variable Dim tWB As Workbook 'temporary workbook (each in directory) Dim tWS As Worksheet 'temporary worksheet variable Dim mWB As Workbook 'master workbook Dim aWS As Worksheet 'active sheet in master workbook Dim RowCount As Long 'Rows used on master sheet Dim uRange As Range 'usedrange for each temporary sheet '***** Set folder to cycle through ***** Path = "c:\" 'Change as needed Application.EnableEvents = False 'turn off events Application.ScreenUpdating = False 'turn off screen updating Set mWB = Workbooks.Add(1) 'create a new one-worksheet workbook Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "" Path = Path & Application.PathSeparator 'add "" End If FileName = Dir(Path & "*.xls", vbNormal) 'set first file's name to filename variable Do Until FileName = "" ' loop until all files have been parsed Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable For Each tWS In tWB.Worksheets 'loop through each sheet Set uRange = tWS.Range("A1", tWS.Cells(tWS.UsedRange.Row + tWS.UsedRange.Rows _ .Count - 1, tWS.UsedRange.Column + tWS.UsedRange.Columns.Count - 1)) 'set used range If RowCount + uRange.Rows.Count > 65536 Then 'if the used range wont fit on the sheet aWS.Columns.AutoFit 'autofit mostly-used worksheet's columns Set aWS = mWB.Sheets.Add(After:=aWS) 'add a new sheet that will accommodate data RowCount = 0 'reset RowCount variable End If If RowCount = 0 Then 'if working with a new sheet aWS.Range("A1", aWS.Cells(1, uRange.Columns.Count)).Value = _ tWS.Range("A1", tWS.Cells(1, uRange.Columns.Count)).Value 'copy headers from tWS RowCount = 1 'add one to rowcount End If aWS.Range("A" & RowCount + 1).Resize(uRange.Rows.Count, uRange.Columns.Count).Value _ = uRange.Value 'move data from temp sheet to data sheet RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly Next 'tWS tWB.Close False 'close temporary workbook without saving FileName = Dir() 'set next file's name to FileName variable Loop aWS.Columns.AutoFit 'autofit columns on last data sheet mWB.Sheets(1).Select 'select first data sheet on master workbook Application.EnableEvents = True 're-enable events Application.ScreenUpdating = True 'turn screen updating back on End Sub
|VBA or Script to identify files which are duplicate in a folder||6||35|
|How can I calculate a balloon payment in Excel?||5||15|
|simple excel formula||4||18|
|IDENTIFY, THEN ADD TOGETHER AND DELETE ONE, EXCEL||3||20|