10 replies
by fenaikh
Hi
please look at these Utilities and articles:
I Utilities:
1- 4TOPS Export Wizard for MS Access 97 v1.0
http://www.zdnet.com/downloads/stories/info/0,,001ALG,.html
2- 4TOPS Chart in Excel Wizard for MS Access 97 v1.0
http://www.zdnet.com/downloads/stories/info/0,,001ALI,.html
3- Easy Report for MS Excel 95 or 97 v2.0
http://www.zdnet.com/downloads/stories/info/0,,0012F2,.html
4- Access Archon - Exporting Access Reports
http://www.zdnet.com/zdhelp/stories/main/0,5594,2214036,00.html
II articles:
1- Exporting Access Data
http://www.zdnet.com/zdhelp/stories/main/0,5594,2626207,00.html
2- Exporting Data to a Text File
http://www.zdnet.com/zdhelp/stories/main/0,5594,2626207-3,00.html
3-Create a custom AutoFormat style
http://www.woodyswatch.com/access/archtemplate.asp?3-01
3- Exporting Data to Another Database
http://www.zdnet.com/zdhelp/stories/main/0,5594,2626207-2,00.html
I hope you like it
good luckMohamed
by peyison
Here are a few options you can try:
1) You can send the report as is to an Excel workbook. If you're not happy with the groupings and totals in the Access report, you can remove them and send the basic data to Excel. You can do this in code usingDoCmd.OutputTo:
DoCmd.OutputTo acOutputReport, sReportName, acFormatXLS, sFileName, False
2) You can write VBA code to send the output of your queries to a CSV (comma separated values) file. A CSV file can be opened by Excel (there is no formatting though). You'd write VBA code to send your output to a text file, separating each field with a comma.
3) You can use Access VBA to open (or create) an Excel workbook and send the data directly to it. This would provide the most control of how the end product would look. You can enter data and format cells all from code. This is also the most complex route, but if you're familiar with VBA, it's not too bad.
If you're interested in more info on any of these options, let me know.
Hope this helps.
by fenaikh
Hi
please make sure no spaces when you copy the
URL's into your browser this is what maybe happend to you. I sent you the first 2 pages thru e-mail.
good luck
Mohamed
by peyison
First off - make sure you have a reference in your VB app to the Excel library.
Project -> References -> Check Microsoft Excel x Object Library
Here are some code samples that will start Excel, put a value in a cell, put a value in a range, save the workbook, and quit without saving: (These should all go in the same module)
'--global declarations of the module/class
Private moExcel As Object
Private moWorkbook As Workbook
Private msWorkBook As String
Private moApplication As Application
Public Sub StartExcel(asWorkbook As String, Optional asPassword As String = "", Optional asPath As String = "", Optional abVisible As Boolean = True)
Dim sPath As String
Dim sFileName As String
If Len(asPath) < 1 Then
sPath = App.Path
Else
sPath = asPath
End If
Set moExcel = CreateObject("Excel.Application")
If Len(asWorkbook) > 0 Then
sFileName = sPath & asWorkbook
If Len(asPassword) > 0 Then
moExcel.Workbooks.Open sFileName, , , , asPassword
Else
moExcel.Workbooks.Open sFileName
End If
Else
moExcel.Workbooks.Add
End If
msWorkBook = moExcel.ActiveWorkbook.Name
Set moApplication = moExcel.Application
If abVisible Then
moApplication.Visible = True
moExcel.Parent.Windows(msWorkBook).Visible = True
End If
Set moWorkbook = moApplication.Workbooks(msWorkBook)
End Sub
more to follow....
by peyison
Public Sub PutValueInCell(asSheetName As String, alRow As Long, alCol As Long, avValue As Variant)
Select Case TypeName(avValue)
Case "Integer"
moApplication.Cells(alRow, alCol).Value = CInt(avValue)
Case "Long"
moApplication.Cells(alRow, alCol).Value = CLng(avValue)
Case "Double", "Decimal", "Currency"
moApplication.Cells(alRow, alCol).Value = CDbl(avValue)
Case "String", "ComboBox", "MaskEdBox"
moApplication.Cells(alRow, alCol).Value= CStr(avValue)
Case Else
MsgBox "Unable to write the value '" & avValue & "' to the workbook.", vbExclamation
End Select
End Sub
Public Sub PutValueInRange(asSheetName As String, asRangeName As String, avValue As Variant)Dim oRng As Range
Dim oName As Name
Set oName = moWorkbook.Names(asRangeName)
Set oRng = oName.RefersToRange
Select Case TypeName(avValue)
Case "Integer"
oRng.Value = CInt(avValue)
Case "Long"
oRng.Value = CLng(avValue)
Case "Double", "Decimal", "Currency"
oRng.Value = CDbl(avValue)
Case "String", "ComboBox", "MaskEdBox"
oRng.Value = CStr(avValue)
Case Else
MsgBox "Unable to write the value '" & avValue & "' to the workbook.", vbExclamation
End Select
Set oName = Nothing
Set oRng = Nothing
End Sub
Public Sub QuitExcel()
moWorkbook.Saved = True
moApplication.Quit
Set moWorkbook = Nothing
Set moApplication = Nothing
Set moExcel = Nothing
End Sub
Public Sub SaveAs(asFileName As String)
On Error Resume Next
Kill asFileName
On Error GoTo 0
moWorkbook.SaveAs asFileName
End Sub
This discussion is closed to new replies.
Trending Windows Forum posts
Sort by Trending