Hi Gary,
adding on to what Graham and others have said ...
here is some "shell" code I reference when I am going to write a program with Excel automation...
'~~~~~~~~~~~~~~~~~~~~~~~~~~ Excel_Conversation
Function Excel_Conversation()
'Crystal (strive4peace)
On Error GoTo Proc_Err
Dim xlApp As Excel.Application, _
booLeaveOpen As Boolean
'if Excel is already open, use that instance
booLeaveOpen = True
'attempting to use something that is not available
'will generate an error
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo Proc_Err
'If xlApp is defined, then we
'already have a conversation
If TypeName(xlApp) = "Nothing" Then
booLeaveOpen = False
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
End If
'Do whatever you want
Proc_Exit:
On Error Resume Next
If TypeName(xlApp) <> "Nothing" Then
xlApp.ActiveWorkbook.Close False
If Not booLeaveOpen Then xlApp.Quit
Set xlApp = Nothing
End If
Exit Function
Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " Excel_Conversation"
Resume Proc_Exit
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~
whatever the state you have set when you save the template XLT file is the way it will be for the user ...
this is what I do:
a. maximize the workbook
b. leave the active cell in a specific place ... either an input cell or a cell that is not a distraction when the workbook is opened.
c. If there are multiple worksheets, do this for each one before you save it and ensure if there are any selections, they are intentional.
d. Make the active sheet where you want the users to start
If your users like graphs, your template could have some graphs that get data from the a particular sheet. When Access writes the values, it could then change the source for the graphs because it would know how many rows were written.
Another thing Access could do before it saves the workbook is format ranges.
~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~
I often use a template to make new workbooks with one sheet ("master"), that I make copies of, fill and rename. I have found the the formatting doesn't always stick in Excel, so this syntax has been very handy for formatting a range:
xlApp.Range(xlSht.Cells(8, 5), xlSht.Cells(nRow + 1, 10)).NumberFormat = "#,##0"
WHERE
nRow is the row counter variable
xlSht is the Excel.Worksheet object
xlApp is the Excel.Application object
'~~~~~~~~~~~~~~~~~~~~~~~~
for putting formulas into Excel instead of calculation results...
with xlSht
.Cells(nRow, 7).Formula = "=IF(E" & nRow & "=0,0,F" & nRow & "/E" & nRow & ")"
'for summing:
.Cells(nRow2, 5).Formula = "=SUM(E" & nRow1 & ":E" & nRow2 - 1 & ")"
end with
WHERE
xlSht is the Excel.Worksheet object
nRow1 and nRow2 are known or calculated row numbers
nRow is the loop counter row number
----
and for putting formulas into Excel instead of calculation results...
with xlSht
.Cells(nRow, 7).Formula = "=IF(E" & nRow & "=0,0,F" & nRow & "/E" & nRow & ")"
.Cells(nRow2, 5).Formula = "=SUM(E" & nRow1 & ":E" & nRow2 - 1 & ")"
end with
WHERE
xlSht is the Excel.Worksheet object
nRow1 and nRow2 are row numbers
"p" is my passed parameter notation -- to modularize the code, I often send a recordset, an Excel object reference, possibly row numbers, etc... to another routine to do the writing to Excel. That way, it is easier to add a loop too ;)
'~~~~~~~~~~~~~~~~~~~~~~~~
here's another handy tip...
to launch Excel code from Access
'this is the workbook with the code if it is somewhere else
xlApp.Workbooks.Open sPath & "PROGRAMS.XLS"
'this is the workbook to run code on, or just to open
xlApp.Workbooks.Open sExcelFile
'run Sub in Programs Workbook if applicable
xlApp.Run "PROGRAMS.XLS!ModuleName.SubName"
'~~~~~~~~~~~~~~~~~~~~~~~~
to make a new workbook based on a template...
xlApp.Workbooks.Add _
Template:= _
CurrentProject.Path _
& "\Templates\Filename.xlt"
'~~~~~~~~~~~~~~~~~~~~~~~~
3. Once the file to send is prepared, it must be attached to en email message and sent.
4. then come the other steps.
Right now, just focus on getting the workbook made. By the time that is done, you will be better able to understand code we can give you for emailing.
Although it only took one short paragraph to explain what you want to do, this is a complex process.
**************************************************************
VBA is the easiest programming language, in my opinion, there is to learn.
Learn VBA
By Crystal
http://www.accessmvp.com/strive4peace/VBA
Excel is used in the 3 chapters that are written for programming examples. The chapters that aren't written (yet?) get into Access -- but the basics of VBA are the same.
This would be excellent for you to read ... no hands on, just reading for these chapters :)
Learning VBA is not hard, it just takes dedication and a bit of time, so plan to spend a day studying -- print the links, get a highlighter, make a nice pot of tea, get comfy in your favorite chair, relax ... and enjoy!
'~~~~~~~~~~~~~~~~~~~~~~~~
CopyFromRecordset code from Nate:
'~~~~~~~~~~~~~~~~~~~
Sub CopyFromRecordset_example( _
pPath As String, pQname As String)
On Error GoTo Proc_Err
' originally posted by NateO
' modified by crystal
' NEEDS REFERENCE
' Microsoft ActiveX Data Objects Library
'Declare your ADO Recordset
Dim rs As ADODB.Recordset
'Excel Objects
' Dim xlApp As Excel.Application 'early binding for dveloping
' Dim xlWb As Excel.Workbook
Dim xlApp As Object 'late binding for distribution
Dim xlWb As Object
'Field Names - Stack into Array
Dim fldArr() As String
'Need some loop counters
Dim j As Long _
, i As Long
Dim sFilename As String
sFilename = pPath & pQname
i = 1
'OLE - Create xl Objects
Set xlApp = New Excel.Application
'Add a new Workbook, with one Worksheet, to our Excel Application
Set xlWb = xlApp.Workbooks.Add(1)
'this is commented out because to show you can loop if you want
'For i = LBound(sqlArr) To UBound(sqlArr)
'New ADO Recordset
Set rs = New ADODB.Recordset
'Open the Recordset, Passing the Sql from our Array
rs.Open CurrentDb.QueryDefs(pQname).SQL, CodeProject.Connection, _
adOpenStatic, adLockReadOnly
With rs
'Stack a String Array with the Field Names
ReDim fldArr(0 To .Fields.Count - 1)
For j = LBound(fldArr) To UBound(fldArr)
Let fldArr(j) = .Fields(j).Name
Next j
'Time to Pass some Data to Excel!
With xlWb.Worksheets
'Add a Worksheet if we're at 2nd Recordset or Greater -- when looping
'If i > 1 Then .Add After:=.Item(i - 1)
'Refer to the Worksheet by Item Number
'in the Collection of Worksheets (1-based)
With .Item(1)
'Pass our dynamic Field String Array to A1,
'stretched to the Right for number of Elements
Let .Range("a1").Resize(, UBound(fldArr) + 1).Value = fldArr
'Copy our Current Recordset to A2
.Range("a2").CopyFromRecordset rs
'Rename Individual Worksheet
.Name = "WorksheetName"
'however many columns of data you have, if desired
.Columns("A:G").EntireColumn.AutoFit
End With
End With
End With
'Moving on, no need to close or terminate our RS, yet,
' we're going to recycle in the Loop
'end of optional loop
'Next
'Make Excel visible - (Otherwise Save and Close)
With xlApp
.Goto xlWb.Worksheets(1).Range("A1")
'commented but can be activated if desired
'xlSht.Cells(1, 1).Activate
'.Visible = True
End With
Save_Workbook:
'delete file if it already exists
If Dir(sFilename) <> "" Then
On Error Resume Next
Kill sFilename
DoEvents
On Error GoTo Proc_Err
End If
'commented because there was not code to disable in this case
'if you are writing to a template with code, you may want
'to disable events in the beginning
'xlApp.EnableEvents True
xlApp.ActiveWorkbook.SaveAs sFilename
xlApp.ActiveWorkbook.Close False
Proc_Exit:
On Error Resume Next
'Terminate our Excel Object Variables
Set xlWb = Nothing
If TypeName(xlApp) <> "Nothing" Then
xlApp.Quit
Set xlApp = Nothing
End If
'Now close and terminate the ADO Recordset, we're all done!!
rs.Close: Set rs = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " CopyFromRecordset_example"
Resume Proc_Exit
'press Ctrl-Break to stop code at Msgbox
'set this to be the next statement then F8 to step through code and debug
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~
Note: Nate said he doesn't like to "hijack" an instance of an application which is why he used New Excel.Application. This was some time back, he might have another method now ;)
'~~~~~~~~~~~~~~~~~~~~~~~~~~ Excel_Conversation
Function Excel_Conversation()
'Crystal (strive4peace)
On Error GoTo Proc_Err
Dim xlApp As Excel.Application, _
booLeaveOpen As Boolean
'if Excel is already open, use that instance
booLeaveOpen = True
'attempting to use something that is not available
'will generate an error
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo Proc_Err
'If xlApp is defined, then we
'already have a conversation
If TypeName(xlApp) = "Nothing" Then
booLeaveOpen = False
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
End If
'Do whatever you want
Proc_Exit:
On Error Resume Next
If TypeName(xlApp) <> "Nothing" Then
xlApp.ActiveWorkbook.Close False
If Not booLeaveOpen Then xlApp.Quit
Set xlApp = Nothing
End If
Exit Function
Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " Excel_Conversation"
Resume Proc_Exit
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~
whatever the state you have set when you save the template XLT file is the way it will be for the user ...
this is what I do:
a. maximize the workbook
b. leave the active cell in a specific place ... either an input cell or a cell that is not a distraction when the workbook is opened.
c. If there are multiple worksheets, do this for each one before you save it and ensure if there are any selections, they are intentional.
d. Make the active sheet where you want the users to start
If your users like graphs, your template could have some graphs that get data from the a particular sheet. When Access writes the values, it could then change the source for the graphs because it would know how many rows were written.
Another thing Access could do before it saves the workbook is format ranges.
~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~
I often use a template to make new workbooks with one sheet ("master"), that I make copies of, fill and rename. I have found the the formatting doesn't always stick in Excel, so this syntax has been very handy for formatting a range:
xlApp.Range(xlSht.Cells(8, 5), xlSht.Cells(nRow + 1, 10)).NumberFormat = "#,##0"
WHERE
nRow is the row counter variable
xlSht is the Excel.Worksheet object
xlApp is the Excel.Application object
'~~~~~~~~~~~~~~~~~~~~~~~~
for putting formulas into Excel instead of calculation results...
with xlSht
.Cells(nRow, 7).Formula = "=IF(E" & nRow & "=0,0,F" & nRow & "/E" & nRow & ")"
'for summing:
.Cells(nRow2, 5).Formula = "=SUM(E" & nRow1 & ":E" & nRow2 - 1 & ")"
end with
WHERE
xlSht is the Excel.Worksheet object
nRow1 and nRow2 are known or calculated row numbers
nRow is the loop counter row number
----
and for putting formulas into Excel instead of calculation results...
with xlSht
.Cells(nRow, 7).Formula = "=IF(E" & nRow & "=0,0,F" & nRow & "/E" & nRow & ")"
.Cells(nRow2, 5).Formula = "=SUM(E" & nRow1 & ":E" & nRow2 - 1 & ")"
end with
WHERE
xlSht is the Excel.Worksheet object
nRow1 and nRow2 are row numbers
"p" is my passed parameter notation -- to modularize the code, I often send a recordset, an Excel object reference, possibly row numbers, etc... to another routine to do the writing to Excel. That way, it is easier to add a loop too ;)
'~~~~~~~~~~~~~~~~~~~~~~~~
here's another handy tip...
to launch Excel code from Access
'this is the workbook with the code if it is somewhere else
xlApp.Workbooks.Open sPath & "PROGRAMS.XLS"
'this is the workbook to run code on, or just to open
xlApp.Workbooks.Open sExcelFile
'run Sub in Programs Workbook if applicable
xlApp.Run "PROGRAMS.XLS!ModuleName.SubName"
'~~~~~~~~~~~~~~~~~~~~~~~~
to make a new workbook based on a template...
xlApp.Workbooks.Add _
Template:= _
CurrentProject.Path _
& "\Templates\Filename.xlt"
'~~~~~~~~~~~~~~~~~~~~~~~~
3. Once the file to send is prepared, it must be attached to en email message and sent.
4. then come the other steps.
Right now, just focus on getting the workbook made. By the time that is done, you will be better able to understand code we can give you for emailing.
Although it only took one short paragraph to explain what you want to do, this is a complex process.
**************************************************************
VBA is the easiest programming language, in my opinion, there is to learn.
Learn VBA
By Crystal
http://www.accessmvp.com/strive4peace/VBA
Excel is used in the 3 chapters that are written for programming examples. The chapters that aren't written (yet?) get into Access -- but the basics of VBA are the same.
This would be excellent for you to read ... no hands on, just reading for these chapters :)
Learning VBA is not hard, it just takes dedication and a bit of time, so plan to spend a day studying -- print the links, get a highlighter, make a nice pot of tea, get comfy in your favorite chair, relax ... and enjoy!
'~~~~~~~~~~~~~~~~~~~~~~~~
CopyFromRecordset code from Nate:
'~~~~~~~~~~~~~~~~~~~
Sub CopyFromRecordset_example( _
pPath As String, pQname As String)
On Error GoTo Proc_Err
' originally posted by NateO
' modified by crystal
' NEEDS REFERENCE
' Microsoft ActiveX Data Objects Library
'Declare your ADO Recordset
Dim rs As ADODB.Recordset
'Excel Objects
' Dim xlApp As Excel.Application 'early binding for dveloping
' Dim xlWb As Excel.Workbook
Dim xlApp As Object 'late binding for distribution
Dim xlWb As Object
'Field Names - Stack into Array
Dim fldArr() As String
'Need some loop counters
Dim j As Long _
, i As Long
Dim sFilename As String
sFilename = pPath & pQname
i = 1
'OLE - Create xl Objects
Set xlApp = New Excel.Application
'Add a new Workbook, with one Worksheet, to our Excel Application
Set xlWb = xlApp.Workbooks.Add(1)
'this is commented out because to show you can loop if you want
'For i = LBound(sqlArr) To UBound(sqlArr)
'New ADO Recordset
Set rs = New ADODB.Recordset
'Open the Recordset, Passing the Sql from our Array
rs.Open CurrentDb.QueryDefs(pQname).SQL, CodeProject.Connection, _
adOpenStatic, adLockReadOnly
With rs
'Stack a String Array with the Field Names
ReDim fldArr(0 To .Fields.Count - 1)
For j = LBound(fldArr) To UBound(fldArr)
Let fldArr(j) = .Fields(j).Name
Next j
'Time to Pass some Data to Excel!
With xlWb.Worksheets
'Add a Worksheet if we're at 2nd Recordset or Greater -- when looping
'If i > 1 Then .Add After:=.Item(i - 1)
'Refer to the Worksheet by Item Number
'in the Collection of Worksheets (1-based)
With .Item(1)
'Pass our dynamic Field String Array to A1,
'stretched to the Right for number of Elements
Let .Range("a1").Resize(, UBound(fldArr) + 1).Value = fldArr
'Copy our Current Recordset to A2
.Range("a2").CopyFromRecordset rs
'Rename Individual Worksheet
.Name = "WorksheetName"
'however many columns of data you have, if desired
.Columns("A:G").EntireColumn.AutoFit
End With
End With
End With
'Moving on, no need to close or terminate our RS, yet,
' we're going to recycle in the Loop
'end of optional loop
'Next
'Make Excel visible - (Otherwise Save and Close)
With xlApp
.Goto xlWb.Worksheets(1).Range("A1")
'commented but can be activated if desired
'xlSht.Cells(1, 1).Activate
'.Visible = True
End With
Save_Workbook:
'delete file if it already exists
If Dir(sFilename) <> "" Then
On Error Resume Next
Kill sFilename
DoEvents
On Error GoTo Proc_Err
End If
'commented because there was not code to disable in this case
'if you are writing to a template with code, you may want
'to disable events in the beginning
'xlApp.EnableEvents True
xlApp.ActiveWorkbook.SaveAs sFilename
xlApp.ActiveWorkbook.Close False
Proc_Exit:
On Error Resume Next
'Terminate our Excel Object Variables
Set xlWb = Nothing
If TypeName(xlApp) <> "Nothing" Then
xlApp.Quit
Set xlApp = Nothing
End If
'Now close and terminate the ADO Recordset, we're all done!!
rs.Close: Set rs = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " CopyFromRecordset_example"
Resume Proc_Exit
'press Ctrl-Break to stop code at Msgbox
'set this to be the next statement then F8 to step through code and debug
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~
Note: Nate said he doesn't like to "hijack" an instance of an application which is why he used New Excel.Application. This was some time back, he might have another method now ;)
'~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~
in this automation sample on RogersAccessLibrary, I took care to comment and use good code -- it would be good to look at too
Document Calculated Fields in Queries, write results to Excel and format
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html
the code is on the page at the bottom and you can also download the BAS file.
Warm Regards,
Crystal
~ have an awesome day ~
On Thursday, March 5, 2015 3:18 PM, "'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
When you add a new sheet to the workbook object, that becomes the activesheet, so you can address it so
xlApp.xlWB.Activesheet
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 05 March 2015 20:44
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Working with Excel tabs
Sent: 05 March 2015 20:44
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Working with Excel tabs
So, when I add a new sheet, Access will use that one until I add a new one?
Thanks,
Gary
__._,_.___
Posted by: Crystal <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar