I know how to open each file, but I'm not sure how to go row by row through the import file and put that data into the template file in the proper columns.
For example, for every row in the input file:
- Data in Column A goes into the template file Column D
- Data in Column F goes into the template file Column B
- And so on...
It has to be done row by row because I have to build some numbering logic in, too. I hope this makes sense.
Here's my code so far:
Code: Select all
Sub CreateTheFile()
Dim strCurrentDirectory As String
strCurrentDirectory = CurDir
Dim strTemplateFilePath As String
strTemplateFilePath = strCurrentDirectory & "\Template\Template.xlsx"
Dim strInputPath As String
strInputPath = strCurrentDirectory & "\Input\"
Dim strOutputPath As String
strOutputPath = strCurrentDirectory & "\Output\"
Debug.Print strCurrentDirectory
Debug.Print strTemplateFilePath
Debug.Print strInputPath
Debug.Print strOutputPath
'Open the input file
Dim strFile As String
strFile = Dir(strInputPath & "*.xls*")
Set wbinput = Workbooks.Open(strInputPath & strFile)
'Sort it
wbinput.Worksheets(1).Sort.SortFields.Clear
wbinput.Worksheets(1).Sort.SortFields.Add2 Key:=Range("I2:I10000" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
wbinput.Worksheets(1).Sort.SortFields.Add2 Key:=Range("A2:A10000" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wbinput.Worksheets(1).Sort
.SetRange Range("A1:X10000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Open the template file
Set wbTemplate = Workbooks.Open(strTemplateFilePath)
'Write the output data
'HERE'S WHERE I"M STUMPED
'Save the template file to output
'Close imput file
Workbooks(strFile).Close savechanges:=False
'Move imput file
FileCopy strInputPath & strFile, strInputPath & "Done\" & Format(Now, "YYYYMMDDHHMM") & "-" & strFile
Kill strInputPath & strFile
'All Done!
MsgBox "Done!"
End Sub