Opening/editing/saving excel file

[cherman]

I am using Access/Excel 2003 and I'm trying to open a file called
Quad_Charts_Template.xls from my C drive, dump some data on one of the
sheets, make a couple changes and save it with a specific name.

This is the code I have to choose my record sorce:
Set rst = CurrentDb.OpenRecordset("qry_Avg_Age_Trend_Source_Data")

The worksheet is called "AAT_Raw_Data", which will always be the 1st sheet
and I want to copy the data from rst onto the sheet starting at A1.

I also want to bold the header row and set the auto filter as active.

And finally I want to save the file as "abc" in the same folder.

I have some code working and some that doesn't so I thought it would be good
to get the complete code from someone.

Any help would be greatly appreciated!

 

[Ken Snell]

See if this article is helpful for getting you started:

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#WriteRstFile

 

[ryguy7272]

Ken's stuff is great! I've used it too many times to count!! Also, check
out this code for using Excel from Access:

Make sure you set a reference to Excel, and then run this code in an Access
module:

Option Compare Database

Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile As String

strFile = "C:\Documents and Settings\Desktop\Ryan\MyWorkbook.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible

Set objExcel = New Excel.Application

objExcel.Visible = True

' Opens up a Workbook

Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile

xlWB.Close

xlapp.Quit

Set xlapp = Nothing

End Sub

HTH,
Ryan---

 

[trevorC via AccessMonster.com]

Hi,
Hope this helps you...

What i can't do is set the print option for a text box created on an excel
sheet via vba code, i can create the text box and do all other text box type
things to it but not set the print box option to off.

This makes an excel file from a query, does stuff to it (create list, format
page) then sends it to the selected recipients (mail_to) without stoping.
(selectable -
comment out .display)

(personal information removed)
Dim strPath As String
Dim rst As DAO.Recordset
Dim AppOutLook
Dim MailOutLook
Dim OlMailItem Set AppOutLook = CreateObject("Outlook.Application")
Set MailOutLook = AppOutLook.CreateItem(olmailItem)
Dim EContent As String
Dim stDocName As String
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Data_Range
Dim Worksheet_Name
Dim db As Database
Dim rs As Recordset

RC = Me.MyCount
If IsEmpty(RC) Then
Exit Sub
End If
Set db = CurrentDb
Set rs = db.OpenRecordset("E-Mail Recipients")
rs.MoveFirst
Do While Not rs.EOF
If rs![Active] = True Then
ttt = rs![E-Mail]
Mail_to_list = Mail_to_list + ttt & ";"
End If
rs.MoveNext
Loop
rs.Close
dt = Format(Forms![main menu]![repairs in report date], " dd-mm-yy")
dd = Format(Now, " hh-mm")
gg = "C:\Dispatch Details\Dispatch Details for - " & ds & dt & dd & ".
xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "daily
repairs in report", gg, True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Workbook.Worksheets(1).Name = "Transfer Details"
Set Current_Worksheet = Excel_Workbook.Worksheets("Transfer Details")
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True

Current_Worksheet.Columns("L:L").Delete Shift:=xlToLeft
Current_Worksheet.Range("L1").FormulaR1C1 = "Date Received"
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Range("A1").FormulaR1C1 = "Reciept Details for " & dt
Current_Worksheet.Rows("1:1").Font.Bold = True
Current_Worksheet.Rows("1:1").Font.Size = 18
Current_Worksheet.Rows("1:1").Font.Name = "Times New Roman"

With Excel_Application.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3"
.Orientation = xlLandscape
.PaperSize = xlPaperA4
End With

rng22 = "B" & 4 & "" & gb1 + 4 ' "$A$5:$D" & Mid
(Current_Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address, 4, 3) - 1
Current_Worksheet.Range(rng22).Select
With selection
Current_Worksheet.ListObjects.Add(xlSrcRange, , xlYes, xlYes).Name =
"List2"
End With

Excel_Workbook.Save
Excel_Application.Quit
T = MsgBox("Select Yes to send the E-Mail now or No to exit without
sending the E-Mail.", vbYesNo, "Send E-Mail Confirmation")
If T = 6 Then
Else
Exit Sub
End If
With MailOutLook
.To = Mail_to_list
.Subject = "Receipt of Units for Repair"
.Attachments.Add gg
.Body = "This is an automatically generated E-Mail " & vbCrLf &
vbCrLf & _
"Attention To - ABC Asset Tracking Department" & vbCr & _
"Attention To - Logistics Department" & vbCr & vbCr & _
"Please find attached Details for the Units returned for
Repair" & vbCrLf & vbCrLf & _
"Comments: -" & Me.Comments & vbCrLf & vbCrLf & vbCrLf & _
"Regards," & vbCrLf & mail_from & _
"Disclaimer" & vbCrLf & _
"This email may contain confidential information."
.Display
SendKeys "%{s}", True '''' only to send automaticaly
End With
End Sub