I would like to add the xls macros below to the module listed. I am
looking for advice as to the best way to go about this.
module
Public Function clean_pn(pn_in As Variant) As String
End Function
Dim pn_out As String
If IsNull(pn_in) Or pn_in = "" Then pn_in = "000"
If pn_in = "VEBA" Then
pn_out = "001"
Else
pn_out = pn_in
End If
clean_pn = pn_out
End Function
Public Sub export_schd04(sXlsPathRoot_P As String, sXlsPathRoot_D As
String, sXlsPathRoot_H As String)
Dim sFundNum As String
Dim sXlsFilePath As String
Dim qdGenXlsTbl As QueryDef
Dim qdDelXlsTbl As QueryDef
Dim rsFundList As Recordset
Dim rsPIW As Recordset
' Dim xlsapp As Application
Dim xlsApp As Excel.Application
Dim xlsWorkBk As Excel.Workbook
Set xlsApp = New Excel.Application
Set rsPIW = CurrentDb.OpenRecordset("t_piw**_export_2004")
Set rsFundList = CurrentDb.OpenRecordset("schd_**fund_list")
Do Until rsFundList.EOF
sFundNum = rsFundList(0)
'
' Do Schedule D
'
Set qdDelXlsTbl = CurrentDb.QueryDefs("schd_purg**e")
qdDelXlsTbl.Execute
Set qdGenXlsTbl =
CurrentDb.QueryDefs("load_schd**_2004_Part1")
qdGenXlsTbl.Parameters("prm_fu**nd_num") = sFundNum
qdGenXlsTbl.Execute
sXlsFilePath = sXlsPathRoot_D & "04d" & sFundNum & "a.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4,
"t_schd_export_2004", sXlsFilePath
Set xlsWorkBk = xlsApp.Workbooks.Open(sXlsFile**Path)
xlsWorkBk.Worksheets(1).Column**s(1).Delete
xlsWorkBk.Worksheets(1).Rows(1**).Delete
xlsWorkBk.Save
xlsWorkBk.Close
Set qdDelXlsTbl = CurrentDb.QueryDefs("schd_purg**e")
qdDelXlsTbl.Execute
Set qdGenXlsTbl =
CurrentDb.QueryDefs("load_schd**_2004_Part2")
qdGenXlsTbl.Parameters("prm_fu**nd_num") = sFundNum
qdGenXlsTbl.Execute
sXlsFilePath = sXlsPathRoot_D & "04d" & sFundNum & "b.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4,
"t_schd_export_2004", sXlsFilePath
Set xlsWorkBk = xlsApp.Workbooks.Open(sXlsFile**Path)
xlsWorkBk.Worksheets(1).Rows(1**).Delete
xlsWorkBk.Save
xlsWorkBk.Close
'
' Do Schedule H
'
Set qdDelXlsTbl = CurrentDb.QueryDefs("schh_purg**e")
qdDelXlsTbl.Execute
Set qdGenXlsTbl = CurrentDb.QueryDefs("load_sch_**h2004")
qdGenXlsTbl.Parameters("prm_fu**nd_num") = sFundNum
qdGenXlsTbl.Execute
sXlsFilePath = sXlsPathRoot_H & "04h" & sFundNum & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4,
"t_schh_export_2004", sXlsFilePath
Set xlsWorkBk = xlsApp.Workbooks.Open(sXlsFile**Path)
xlsWorkBk.Worksheets(1).Rows(1**).Delete
xlsWorkBk.Save
xlsWorkBk.Close
'
' Do Plan Information Worksheet
'
' rsPIW.MoveFirst
' rsPIW.Edit
' rsPIW.Fields("g") = Mid(rsFundList.Fields("fund_ei**n"),
1,
2)
' rsPIW.Fields("h") = Mid(rsFundList.Fields("fund_ei**n"),
3,
7)
' rsPIW.Fields("i") = Left(rsFundList.Fields("fund_n**m"),
48)
' rsPIW.Fields("j") = Left(rsFundList.Fields("fund_n**m"),
48)
' rsPIW.Update
' sXlsFilePath = sXlsPathRoot_P & "p" & sFundNum & ".xls"
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4,
"t_piw_export_2000", sXlsFilePath
' Set xlsWorkBk = xlsApp.Workbooks.Open(sXlsFile**Path)
' xlsWorkBk.Worksheets(1).Rows(1**).Delete
' xlsWorkBk.Save
' xlsWorkBk.Close
rsFundList.MoveNext
Loop
MsgBox "Files Generated", vbOKOnly, ""
End Sub
------------------------------**-----------------------------*-*-------
xls macro A
Sch_D Part 1.
Sub CreateFile()
Dim SN, LN As Variant
Dim Counter, SR, SC As Integer
'SN = Start Name, LN = Last Name, SR = Start Row, SC = Start Column
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.ReferenceStyle = xlR1C1
LN = Range("A65536").End(xlUp).Row
SR = 9
SC = 1
Counter = 0
For SN = 9 To LN
Range("A" & SN & ":D" & SN).Select
Selection.Copy
Cells(SR, SC).Select
ActiveSheet.Paste
SC = SC + 4
Counter = Counter + 1
If Counter = 8 Then
SR = SR + 1
Counter = 0
SC = 1
Else: SR = SR
End If
Next
Application.ReferenceStyle = xlA1
Range("AG" & 9, "AG" & SR) = "~5x4"
Range("A" & SR + 1, "D" & 65536).Select
Selection.ClearContents
Range("A1").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
------------------------------**-----------------------------*-*-------
xls Macro 2
Sch_D_Part 2
Sub createfile_2()
Dim SN, LN As Variant
Dim Counter, SR, SC As Integer
'SN = Start Name, LN = Last Name, SR = Start Row, SC = Start Column
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.ReferenceStyle = xlR1C1
LN = Range("A65536").End(xlUp).Row
SR = 9
SC = 1
Counter = 0
For SN = 9 To LN
Range("A" & SN & ":F" & SN).Select
Selection.Copy
Cells(SR, SC).Select
ActiveSheet.Paste
SC = SC + 6
Counter = Counter + 1
If Counter = 6 Then
SR = SR + 1
Counter = 0
SC = 1
Else: SR = SR
End If
Next
Application.ReferenceStyle = xlA1
Range("AK" & 9, "AK" & SR) = "~3x4"
Range("A" & SR + 1, "F" & 65536).Select
Selection.ClearContents
Range("A1").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
------------------------------**-----------------------------*-*-------
' Do Schedule D
'
Set qdDelXlsTbl = CurrentDb.QueryDefs("schd_purg**e")
qdDelXlsTbl.Execute
Set qdGenXlsTbl =
CurrentDb.QueryDefs("load_schd**_2004_Part1")
qdGenXlsTbl.Parameters("prm_fu**nd_num") = sFundNum
qdGenXlsTbl.Execute
sXlsFilePath = sXlsPathRoot_D & "04d" & sFundNum & "a.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4,
"t_schd_export_2004", sXlsFilePath
Set xlsWorkBk = xlsApp.Workbooks.Open(sXlsFile**Path)
xlsWorkBk.Worksheets(1).Column**s(1).Delete
xlsWorkBk.Worksheets(1).Rows(1**).Delete
<<<<I WOULD LIKE MACRO A TO RUN HERE>>>
xlsWorkBk.Save
xlsWorkBk.Close
Set qdDelXlsTbl = CurrentDb.QueryDefs("schd_purg**e")
qdDelXlsTbl.Execute
Set qdGenXlsTbl =
CurrentDb.QueryDefs("load_schd**_2004_Part2")
qdGenXlsTbl.Parameters("prm_fu**nd_num") = sFundNum
qdGenXlsTbl.Execute
sXlsFilePath = sXlsPathRoot_D & "04d" & sFundNum & "b.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4,
"t_schd_export_2004", sXlsFilePath
Set xlsWorkBk = xlsApp.Workbooks.Open(sXlsFile**Path)
xlsWorkBk.Worksheets(1).Rows(1**).Delete
<<<<I WOULD LIKE MACRO B TO RUN HERE>>>
xlsWorkBk.Save
xlsWorkBk.Close
Any advice would be appreciated.
