I have a table that is exported to Excel spreadsheet. The values are then
selected, cut and pasted into a new sheet. I'm having "does not support this
method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
commands:


Set xlx = CreateObject("Excel.Application")
xlx.Visible = True 'False use after development
Set xlw = xlx.Workbooks.Open(FileName)
Set xl = xlw.WorkSheets(1)
xl.NAME = "Mass Void Form EE " & D
xl.Range("H1").Select
X = xlx.activecell.Column
Y = xlx.activecell.Row
xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
causes error
xl.activecell.End(xldown).Select <!--
This causes error

What's the correct syntax, and more importantly, does anyone have a source
that lists the Excel objects commands. I've made translations from Excel
macros for most of my commands, but obvious others are not translating.

TIA

--
Chris Freeman
IT Project Coordinator

Oops,

The correct coding problems are this:

xlx.activecell.End(xlDown).Select <!-- Causes error
and
xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select

Sorry about that


--
Chris Freeman
IT Project Coordinator


"Chris Freeman" wrote:

> I have a table that is exported to Excel spreadsheet. The values are then
> selected, cut and pasted into a new sheet. I'm having "does not support this
> method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> commands:
>
>
> Set xlx = CreateObject("Excel.Application")
> xlx.Visible = True 'False use after development
> Set xlw = xlx.Workbooks.Open(FileName)
> Set xl = xlw.WorkSheets(1)
> xl.NAME = "Mass Void Form EE " & D
> xl.Range("H1").Select
> X = xlx.activecell.Column
> Y = xlx.activecell.Row
> xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
> causes error
> xl.activecell.End(xldown).Select <!--
> This causes error
>
> What's the correct syntax, and more importantly, does anyone have a source
> that lists the Excel objects commands. I've made translations from Excel
> macros for most of my commands, but obvious others are not translating.
>
> TIA
>
> --
> 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''.


"Chris Freeman" wrote:

> Oops,
>
> The correct coding problems are this:
>
> xlx.activecell.End(xlDown).Select <!-- Causes error
> and
> xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
>
> Sorry about that
>
>
> --
> Chris Freeman
> IT Project Coordinator
>
>
> "Chris Freeman" wrote:
>
> > I have a table that is exported to Excel spreadsheet. The values are then
> > selected, cut and pasted into a new sheet. I'm having "does not support this
> > method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> > commands:
> >
> >
> > Set xlx = CreateObject("Excel.Application")
> > xlx.Visible = True 'False use after development
> > Set xlw = xlx.Workbooks.Open(FileName)
> > Set xl = xlw.WorkSheets(1)
> > xl.NAME = "Mass Void Form EE " & D
> > xl.Range("H1").Select
> > X = xlx.activecell.Column
> > Y = xlx.activecell.Row
> > xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
> > causes error
> > xl.activecell.End(xldown).Select <!--
> > This causes error
> >
> > What's the correct syntax, and more importantly, does anyone have a source
> > that lists the Excel objects commands. I've made translations from Excel
> > macros for most of my commands, but obvious others are not translating.
> >
> > TIA
> >
> > --
> > Chris Freeman
> > IT Project Coordinator

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


"ryguy7272" wrote:

> 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''.
>
>
> "Chris Freeman" wrote:
>
> > Oops,
> >
> > The correct coding problems are this:
> >
> > xlx.activecell.End(xlDown).Select <!-- Causes error
> > and
> > xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
> >
> > Sorry about that
> >
> >
> > --
> > Chris Freeman
> > IT Project Coordinator
> >
> >
> > "Chris Freeman" wrote:
> >
> > > I have a table that is exported to Excel spreadsheet. The values are then
> > > selected, cut and pasted into a new sheet. I'm having "does not support this
> > > method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> > > commands:
> > >
> > >
> > > Set xlx = CreateObject("Excel.Application")
> > > xlx.Visible = True 'False use after development
> > > Set xlw = xlx.Workbooks.Open(FileName)
> > > Set xl = xlw.WorkSheets(1)
> > > xl.NAME = "Mass Void Form EE " & D
> > > xl.Range("H1").Select
> > > X = xlx.activecell.Column
> > > Y = xlx.activecell.Row
> > > xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
> > > causes error
> > > xl.activecell.End(xldown).Select <!--
> > > This causes error
> > >
> > > What's the correct syntax, and more importantly, does anyone have a source
> > > that lists the Excel objects commands. I've made translations from Excel
> > > macros for most of my commands, but obvious others are not translating.
> > >
> > > TIA
> > >
> > > --
> > > 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''.


"Chris Freeman" wrote:

> 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
>
>
> "ryguy7272" wrote:
>
> > 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''.
> >
> >
> > "Chris Freeman" wrote:
> >
> > > Oops,
> > >
> > > The correct coding problems are this:
> > >
> > > xlx.activecell.End(xlDown).Select <!-- Causes error
> > > and
> > > xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
> > >
> > > Sorry about that
> > >
> > >
> > > --
> > > Chris Freeman
> > > IT Project Coordinator
> > >
> > >
> > > "Chris Freeman" wrote:
> > >
> > > > I have a table that is exported to Excel spreadsheet. The values are then
> > > > selected, cut and pasted into a new sheet. I'm having "does not support this
> > > > method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> > > > commands:
> > > >
> > > >
> > > > Set xlx = CreateObject("Excel.Application")
> > > > xlx.Visible = True 'False use after development
> > > > Set xlw = xlx.Workbooks.Open(FileName)
> > > > Set xl = xlw.WorkSheets(1)
> > > > xl.NAME = "Mass Void Form EE " & D
> > > > xl.Range("H1").Select
> > > > X = xlx.activecell.Column
> > > > Y = xlx.activecell.Row
> > > > xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
> > > > causes error
> > > > xl.activecell.End(xldown).Select <!--
> > > > This causes error
> > > >
> > > > What's the correct syntax, and more importantly, does anyone have a source
> > > > that lists the Excel objects commands. I've made translations from Excel
> > > > macros for most of my commands, but obvious others are not translating.
> > > >
> > > > TIA
> > > >
> > > > --
> > > > Chris Freeman
> > > > IT Project Coordinator

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:P").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


"ryguy7272" wrote:

> 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''.
>
>
> "Chris Freeman" wrote:
>
> > 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
> >
> >
> > "ryguy7272" wrote:
> >
> > > 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''.
> > >
> > >
> > > "Chris Freeman" wrote:
> > >
> > > > Oops,
> > > >
> > > > The correct coding problems are this:
> > > >
> > > > xlx.activecell.End(xlDown).Select <!-- Causes error
> > > > and
> > > > xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
> > > >
> > > > Sorry about that
> > > >
> > > >
> > > > --
> > > > Chris Freeman
> > > > IT Project Coordinator
> > > >
> > > >
> > > > "Chris Freeman" wrote:
> > > >
> > > > > I have a table that is exported to Excel spreadsheet. The values are then
> > > > > selected, cut and pasted into a new sheet. I'm having "does not support this
> > > > > method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> > > > > commands:
> > > > >
> > > > >
> > > > > Set xlx = CreateObject("Excel.Application")
> > > > > xlx.Visible = True 'False use after development
> > > > > Set xlw = xlx.Workbooks.Open(FileName)
> > > > > Set xl = xlw.WorkSheets(1)
> > > > > xl.NAME = "Mass Void Form EE " & D
> > > > > xl.Range("H1").Select
> > > > > X = xlx.activecell.Column
> > > > > Y = xlx.activecell.Row
> > > > > xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
> > > > > causes error
> > > > > xl.activecell.End(xldown).Select <!--
> > > > > This causes error
> > > > >
> > > > > What's the correct syntax, and more importantly, does anyone have a source
> > > > > that lists the Excel objects commands. I've made translations from Excel
> > > > > macros for most of my commands, but obvious others are not translating.
> > > > >
> > > > > TIA
> > > > >
> > > > > --
> > > > > Chris Freeman
> > > > > IT Project Coordinator

Sounds like the story has changed, or I totally misunderstood from the
beginning. If you are splitting data sets look at this:
http://www.datapigtechnologies.com/f...explosion.html
http://www.datapigtechnologies.com/AccessMain.htm
(find the file named DataPig Access Explosion)

For Excel, see this:
http://www.rondebruin.nl/copy5.htm

If those references don't help, I don't think I will be able to help you.
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Chris Freeman" wrote:

> 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:P").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
>
>
> "ryguy7272" wrote:
>
> > 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''.
> >
> >
> > "Chris Freeman" wrote:
> >
> > > 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
> > >
> > >
> > > "ryguy7272" wrote:
> > >
> > > > 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''.
> > > >
> > > >
> > > > "Chris Freeman" wrote:
> > > >
> > > > > Oops,
> > > > >
> > > > > The correct coding problems are this:
> > > > >
> > > > > xlx.activecell.End(xlDown).Select <!-- Causes error
> > > > > and
> > > > > xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
> > > > >
> > > > > Sorry about that
> > > > >
> > > > >
> > > > > --
> > > > > Chris Freeman
> > > > > IT Project Coordinator
> > > > >
> > > > >
> > > > > "Chris Freeman" wrote:
> > > > >
> > > > > > I have a table that is exported to Excel spreadsheet. The values are then
> > > > > > selected, cut and pasted into a new sheet. I'm having "does not support this
> > > > > > method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> > > > > > commands:
> > > > > >
> > > > > >
> > > > > > Set xlx = CreateObject("Excel.Application")
> > > > > > xlx.Visible = True 'False use after development
> > > > > > Set xlw = xlx.Workbooks.Open(FileName)
> > > > > > Set xl = xlw.WorkSheets(1)
> > > > > > xl.NAME = "Mass Void Form EE " & D
> > > > > > xl.Range("H1").Select
> > > > > > X = xlx.activecell.Column
> > > > > > Y = xlx.activecell.Row
> > > > > > xl.Range(.activecell, .activecell.xlEnd).Select <!-- This
> > > > > > causes error
> > > > > > xl.activecell.End(xldown).Select <!--
> > > > > > This causes error
> > > > > >
> > > > > > What's the correct syntax, and more importantly, does anyone have a source
> > > > > > that lists the Excel objects commands. I've made translations from Excel
> > > > > > macros for most of my commands, but obvious others are not translating.
> > > > > >
> > > > > > TIA
> > > > > >
> > > > > > --
> > > > > > Chris Freeman
> > > > > > IT Project Coordinator

Ryan,
No problem. Turns out one good turn deserves another. I posted another issue
in the forms Coding board, how to do a SaveAs to convert a file to *.csv.

things weren't working there as well, and then I came acorss a post that led
me to a website: http://www.pcreview.co.uk/forums/thread-1924416.php

That got things working, but only partially, which led me to investigate my
references. I thought I had everything set, but realized that my MS Office
11.0 object library did not include excel objects. Once i added the Excel
object library, things worked correctly, with a slight correction:
xlx.ActiveCell.End(xlDown).Row became xlx.ActiveCell.End(xlDown).Select and
now it all works just fine.

Thanks for the excellent road trip
--
Chris Freeman
IT Project Coordinator


"ryguy7272" wrote:

> Sounds like the story has changed, or I totally misunderstood from the
> beginning. If you are splitting data sets look at this:
> http://www.datapigtechnologies.com/f...explosion.html
> http://www.datapigtechnologies.com/AccessMain.htm
> (find the file named DataPig Access Explosion)
>
> For Excel, see this:
> http://www.rondebruin.nl/copy5.htm
>
> If those references don't help, I don't think I will be able to help you.
> Ryan---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Chris Freeman" wrote:
>
> > 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:P").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
> >
> >
> > "ryguy7272" wrote:
> >
> > > 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''.
> > >
> > >
> > > "Chris Freeman" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "ryguy7272" wrote:
> > > >
> > > > > 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''.
> > > > >
> > > > >
> > > > > "Chris Freeman" wrote:
> > > > >
> > > > > > Oops,
> > > > > >
> > > > > > The correct coding problems are this:
> > > > > >
> > > > > > xlx.activecell.End(xlDown).Select <!-- Causes error
> > > > > > and
> > > > > > xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
> > > > > >
> > > > > > Sorry about that
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Chris Freeman
> > > > > > IT Project Coordinator
> > > > > >
> > > > > >
> > > > > > "Chris Freeman" wrote:
> > > > > >
> > > > > > > I have a table that is exported to Excel spreadsheet. The values are then
> > > > > > > selected, cut and pasted into a new sheet. I'm having "does not support this
> > > > > > > method" errors with the Activecell.SpecialCells and activecell.End(xlDown)
> > > > > > > commands:
> > > > > > >
> > > > > > >
> > > > > > > Set xlx = CreateObject("Excel.Application")
> > > > > > > xlx.Visible = True 'False use after development
> > > > > > > Set xlw = xlx.Workbooks.Open(FileName)
> > > > > > > Set xl = xlw.WorkSheets(1)
> > > > > > > xl.NAME = "Mass Void Form EE " & D
> > > > > > > xl.Range("H1").Select

Ah! Glad you got it working Chris. Please give a few green checks if my
posts were helpful.

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Chris Freeman" wrote:

> Ryan,
> No problem. Turns out one good turn deserves another. I posted another issue
> in the forms Coding board, how to do a SaveAs to convert a file to *.csv.
>
> things weren't working there as well, and then I came acorss a post that led
> me to a website: http://www.pcreview.co.uk/forums/thread-1924416.php
>
> That got things working, but only partially, which led me to investigate my
> references. I thought I had everything set, but realized that my MS Office
> 11.0 object library did not include excel objects. Once i added the Excel
> object library, things worked correctly, with a slight correction:
> xlx.ActiveCell.End(xlDown).Row became xlx.ActiveCell.End(xlDown).Select and
> now it all works just fine.
>
> Thanks for the excellent road trip
> --
> Chris Freeman
> IT Project Coordinator
>
>
> "ryguy7272" wrote:
>
> > Sounds like the story has changed, or I totally misunderstood from the
> > beginning. If you are splitting data sets look at this:
> > http://www.datapigtechnologies.com/f...explosion.html
> > http://www.datapigtechnologies.com/AccessMain.htm
> > (find the file named DataPig Access Explosion)
> >
> > For Excel, see this:
> > http://www.rondebruin.nl/copy5.htm
> >
> > If those references don't help, I don't think I will be able to help you.
> > Ryan---
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Chris Freeman" wrote:
> >
> > > 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:P").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
> > >
> > >
> > > "ryguy7272" wrote:
> > >
> > > > 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''.
> > > >
> > > >
> > > > "Chris Freeman" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "ryguy7272" wrote:
> > > > >
> > > > > > 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''.
> > > > > >
> > > > > >
> > > > > > "Chris Freeman" wrote:
> > > > > >
> > > > > > > Oops,
> > > > > > >
> > > > > > > The correct coding problems are this:
> > > > > > >
> > > > > > > xlx.activecell.End(xlDown).Select <!-- Causes error
> > > > > > > and
> > > > > > > xl.Range(xlx.activecell, xlx.activecell.SpecialCells(xlLastCell)).Select
> > > > > > >