473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Module Help!

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.

Nov 13 '05 #1
4 2187
I know almost nothing about automating Excel, but read this article....
http://www.mvps.org/access/modules/mdl0007.htm

Nov 13 '05 #2
I know almost nothing about automating Excel, but read this article....
http://www.mvps.org/access/modules/mdl0007.htm

Nov 13 '05 #3
Br
jp******@ibtco.com wrote:
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.
<>
Any advice would be appreciated.


You'd need to reference Excel (in an Access module go to
Tools/References in the menu).
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #4
Br
jp******@ibtco.com wrote:
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.
<>
Any advice would be appreciated.


You'd need to reference Excel (in an Access module go to
Tools/References in the menu).
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Steven | last post by:
I'm writing a Python script which can be called from the command-line, and I want to use my module doc string as the CL help text, but I don't know how to access my module object from inside the...
1
by: Peter Åstrand | last post by:
There's a new PEP available: PEP 324: popen5 - New POSIX process module A copy is included below. Comments are appreciated. ---- PEP: 324 Title: popen5 - New POSIX process module
6
by: Sean Berry | last post by:
I don't know that this partilarily bad programming, but I was interested in doing the following. def functionname( module, var1, var2 ): import module I would like to be able to pass the...
3
by: sean | last post by:
In article <cyH8c.3460$GH3.601@fed1read07>, sean@sands.beach.net (Sean Berry) writes: > I don't know that this partilarily bad programming, > but I was interested in doing the following. > > def...
3
by: Rob McCrea | last post by:
Hi all, On windows98SE, running Python 2.3.4 (#53, May 25 2004, 21:17:02) on win32, the built-in help() function gives me a deprecation warning when used on my docstrings. just help() start...
4
by: Lonnie Princehouse | last post by:
I've run into some eccentric behavior... It appears that one of my modules is being cut off at exactly 2^14 characters when I try to import it. Has anyone else encountered this? I can't find any...
2
by: rh0dium | last post by:
Hi all, So I have a slice of code which calls other python code. I have started to take a real liking to the logging module, but I want to extend this into the called python code. I have no...
1
by: alain MONTMORY | last post by:
Hello everybody, I am a newbie to python so I hope I am at the right place to expose my problem..... :-http://www.python.org/doc/2.4.2/ext/pure-embedding.html 5.3 Pure Embedding I download the...
6
by: JonathanOrlev | last post by:
Hello everyone, I have a newbe question: In Access (2003) VBA, what is the difference between a Module and a Class Module in the VBA development environment? If I remember correctly, new...
0
by: Lie | last post by:
Yesterday I installed compiz-icon in my Ubuntu. Today, when I go to the python interpreter, I happen to do this: ### START OF PYTHON SESSION ### Python 2.5.2 (r252:60911, Apr 21 2008, 11:17:30)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.