Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing multiple files
I am trying to build code. I am just a beginner with VBA. I am trying to incorporate a colon delimiting option into this code when it brings in all files. Also, I am trying to code a "save as" statement into the code at the end. How do I go about this ? Sub Import() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long Dim Fnum As Long Dim mybook As Workbook Dim basebook As Workbook 'Fill in the path\folder where the files are 'on your machine MyPath = "c:\Data" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.asc") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If On Error GoTo CleanUp Application.ScreenUpdating = False Set basebook = ThisWorkbook 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) On Error Resume Next ActiveSheet.Name = mybook.Name On Error GoTo 0 ' You can use this if you want to copy only the values ' With ActiveSheet.UsedRange ' .Value = .Value ' End With mybook.Close savechanges:=False Next Fnum End If CleanUp: Application.ScreenUpdating = True End Sub -- schnett ------------------------------------------------------------------------ schnett's Profile: http://www.excelforum.com/member.php...o&userid=12035 View this thread: http://www.excelforum.com/showthread...hreadid=543771 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing multiple files | Excel Discussion (Misc queries) | |||
Importing multiple files | Excel Discussion (Misc queries) | |||
importing multiple .dat files! | Excel Worksheet Functions | |||
Importing from multiple text files | Excel Programming | |||
Importing Multiple files nearly there | Excel Programming |