Ryan,
Maybe I'm not making this clear, so I'm posting all the code and the story
behind this: checks are requested in our database, and that info resides in a
table. the table contents are then exported to an Excel sheet to be sent to
the Funds team. The Funds team voids the checks and returns the file marked
void. We want to break the file out by individual Client ID so each team
member cam see their voids. there could be thousands of checks in the
table/file. The code concerning the Excel formatting is below:
DBPath = currdb.Properties![Data Source Name]
If InStr(DBPath, "All Letters Sent") Then
Pos = InStr(DBPath, "All Letters Sent") - 1
End If
DataDir = Left(DBPath, Pos)
Folder = "\\Sdfilsrv1\TrustAcct\Escheat\Escheatment Service\All
Letters Sent\Exports\FTS Mass Voids\"
D = Format(DATE, "yymmdd")
DT = Format(Now, "yyddmm_hhmmss")
File = "FTS_Mass_Void_FormEE_" & DT & ".xls"
FileName = Folder & File
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"xqry_Mass_Void_Form_EE", FileName, True
'run update to insert date into Void Export Date field in check
Reissue table
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Check_Reissue SET tbl_Check_Reissue.[Void
Export Date] = Date() WHERE " & _
"(((tbl_Check_Reissue.[Sent to Void])=True) AND
((tbl_Check_Reissue.[Void Export Date])=False Or " & _
"(tbl_Check_Reissue.[Void Export Date]) Is Null) AND
((tbl_Check_Reissue.[Void Type])<>'999' Or " & _
"(tbl_Check_Reissue.[Void Type])='FALSE' Or
(tbl_Check_Reissue.[Void Type]) Is Null));"
DoCmd.SetWarnings True
'open and close excel and the file before user views to make
corrections
Set xlx = CreateObject("Excel.Application")
xlx.Visible = False
Set xlw = xlx.Workbooks.Open(FileName)
Set xl = xlw.Worksheets(1)
xl.NAME = "Mass Void Form EE " & D
xl.Range("1:1").Delete
xl.Range("M
").Delete
xl.Range("A1").Select
xl.Columns("A:L").AutoFit
T = MsgBox("Do you want to create void forms?", vbYesNo)
If T = vbYes Then
WS = xlw.Worksheets(1).NAME
xl.Range("H1").Select
X = xlx.ActiveCell.Column
Y = xlx.ActiveCell.Row
'error xlx.ActiveCell.End(xlDown).Row
'error xl.Range(xlx.activecell,
xlx.activecell.SpecialCells(xlLastCell)).Select
xlx.Selection.Sort Key1:=xl.Range("H1")
SN = 1
CC = xlx.ActiveCell.Value
C1 = xlx.ActiveCell.Cells.ADDRESS
Do
If xlx.ActiveCell.Offset(1, 0).Value = CC Then
xlx.ActiveCell.Offset(1, 0).Select
C2 = xlx.ActiveCell.Cells.ADDRESS '
xlwsSheet1.Application.ActiveCell.Cells.Address
Else
'Do
' xlx.activecell.offset(1, 0).Select
' Else
'Loop Until xlx.activecell.offset(1, 0).Value <> CC
CC = xlx.ActiveCell.Value
xlw.Worksheets.Add
xlw.Sheets("Sheet" & SN).Select
xlw.Sheets("Sheet" & SN).NAME = CC
xlw.Sheets(WS).Select
xl.Range(C1, C2).Select
xlx.Selection.EntireRow.Copy 'Destination:=xlw.Sheets(CC)
xlw.Sheets(CC).Select
xlx.ActiveSheet.Paste
xlx.CutCopyMode = False
SN = SN + 1
xlw.Sheets(WS).Select
xl.Range(C2).Select
xlx.ActiveCell.Offset(1, 0).Select
C1 = xlx.ActiveCell.Cells.ADDRESS
CC = xlx.ActiveCell.Value
End If
Loop Until xlx.ActiveCell.Value = ""
End If
xlw.Save
xlx.DisplayAlerts = False
xlw.Close
xlx.Quit
Set xlx = Nothing
Set xlw = Nothing
Set xl = Nothing
'On Error GoTo Excel_Open
UA = MsgBox("Export of FTS Mass Void records into Excel format
successful!" & vbCrLf & "" & vbCrLf & _
"Would you like to review the file in Excel?", vbYesNo, "FTS Mass
Void Export Process")
If UA = vbYes Then
'open excel and the file
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open(FileName)
Set xlx = Nothing
Set xlw = Nothing
End If
--
Chris Freeman
IT Project Coordinator
:
Exporting from Excel and running from Access? Sounds like this is what you
need...
It is quite easy to perform operations in Excel, and control the entire
process 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 ControlExcelFromAccess()
Dim strFile As String
strFile = "C:\Documents and Settings\ryan\Desktop\Ryan\Crosstab Query.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 even 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
So, just look for the part that says 'Your Excel code begins here...'.
Record a macro or do whatever you need to do, in that section, and the code
should run fine.
HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
:
Ryan,
I figured I was in Access Programming versus Forms Coding since this was
dealing with an external object outside of the form. If that's an incorrect
assumption, then sorry about the intrusion.
Anyway, all the methods listed below failed, generating 'Function Not
Defined' errors on the word 'Cells':
LastRow = Cells(Rows.Count, activecell.Column).End(xlUp).Row
MsgBox Cells(Rows.Count, "A").End(xlUp).Row
Again, I'm exporting the spreadsheet to Excel, then wanting to run the code
from Access to manipulate the data instead of using a macro from an Excel
file. This way Access runs the process instead of users having to click
another button in excel, or having to setup the sheet on everyones computer.
--
Chris Freeman
IT Project Coordinator
:
You know you are in an Access Programming DG, right.
This is the basic concept:
LastRow = Cells(Rows.Count, activecell.column).End(xlUp).Row
Or, something liek this:
Sub Test()
lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lRow
End Sub
Here is another method:
Sub MessageRow()
MsgBox Cells(Rows.Count, "A").End(xlUp).Row
End Sub
One more, for good measure:
Sub SelRow()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LastRow).Select
End Sub
HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
:
Oops,
The correct coding problems are this:
xlx.activecell.End(xlDown).Select <!-- Causes error
and
xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
