i have a bunch of codes i've done but i've encounter compile error: only commments may appear after end sub, end function or end property. i got the error after the part "Function getNewID(OldID As String) As String", i do know that the message box or functions have to be declared in sub or functions, i need help for this error what to do as i don't really know what to put for my next module start from Dim myFfile As String, the file footer part.
Code:
Sub CreatePFHeaderFooter()
Dim x As Long, y As Long
Dim data(1 To 29) As String
Dim myfile As String
'file location
myfile = "C:\FileHeader.xls"
Application.Workbooks.Open FileName:=myfile
DatFile1Name = ThisWorkbook.Path + "\File01.txt"
Open DatFile1Name For Output As #1 'create csv file
x = 2
While Cells(x, 1).Value <> ""
If Cells(x, 3) = "" Then Cells(x, 3) = getNewID(Cells(x - 1, 3))
For y = 1 To 28
data(y) = Cells(x, y)
Next
Print #1, Join(data, "|")
x = x + 1
Wend
Close #1
ActiveWorkbook.Close
MsgBox ("File File01.TXT created")
End Sub
Function getNewID(OldID As String) As String
Dim arr() As String, strDate As String
Dim d As Date
arr = Split(OldID, "_")
strDate = arr(1)
d = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
If d = Date Then
arr(2) = Format(CInt(arr(3)) + 1, "000")
Else
arr(1) = Format(Date, "yyyymmdd")
arr(2) = "001"
End If
getNewID = Join(arr, "_")
End Function
Dim myFfile As String
myFfile = "C:\FileFooter.xls"
Application.Workbooks.Open FileName:=myFfile
'With Application.FileDialog(msoFileDialogOpen)
' .Filters.Add "Excel Workbooks", "*.xlsx", 1 'only excel workbooks are shown
' .Title = "Select BT Serial Number Report"
' .Show
' ' Display paths of each file selected
' For lngcount = 1 To .SelectedItems.Count
' FileName = .SelectedItems(lngcount)
' Next lngcount
'End With
'Workbooks.Open(FileName).Activate
'Set SourceWB = ActiveWorkbook
DatFile1Name = ThisWorkbook.Path + "\File03.txt"
Open DatFile1Name For Output As #2 'create csv file
vRow = 2
While Cells(vRow, 1).Value <> ""
field1 = Cells(vRow, 1).Value
field2 = Cells(vRow, 2).Value
Field3 = Cells(vRow, 3).Value
field4 = Cells(vRow, 4).Value
field5 = Cells(vRow, 5).Value
field6 = Cells(vRow, 6).Value
field7 = Cells(vRow, 7).Value
field8 = Cells(vRow, 8).Value
field9 = Cells(vRow, 9).Value
field10 = Cells(vRow, 10).Value
field11 = Cells(vRow, 11).Value
field12 = Cells(vRow, 12).Value
field13 = Cells(vRow, 13).Value
field14 = Cells(vRow, 14).Value
field15 = Cells(vRow, 15).Value
field16 = Cells(vRow, 16).Value
field17 = Cells(vRow, 17).Value
field18 = Cells(vRow, 18).Value
field19 = Cells(vRow, 19).Value
field20 = Cells(vRow, 20).Value
field21 = Cells(vRow, 21).Value
field22 = Cells(vRow, 22).Value
field23 = Cells(vRow, 23).Value
field24 = Cells(vRow, 24).Value
field25 = Cells(vRow, 25).Value
field26 = Cells(vRow, 26).Value
field27 = Cells(vRow, 27).Value
field28 = Cells(vRow, 28).Value
field29 = Cells(vRow, 29).Value
'Dim str As String
str = ""
str = field1 & "|" & field2 & "|" & Field3 & "|" & field4 & "|" & field5 & "|" & field6 & "|" & field7 & "|" & field8 & "|" & field9 & "|" & field10 & "|"
str = str & field11 & "|" & field12 & "|" & field13 & "|" & field14 & "|" & field15 & "|" & field16 & "|" & field17 & "|" & field18 & "|" & field19 & "|" & field20 & "|"
str = str & field21 & "|" & field22 & "|" & field23 & "|" & field24 & "|" & field25 & "|" & field26 & "|" & field27 & "|" & field28 & "|" & field29 & "|"
Print #2, str
vRow = vRow + 1
Wend
Close #2
ActiveWorkbook.Close
MsgBox ("File03.TXT created")
End