Code won't work (1 Viewer)

[Robgould]

I was wondering if anyone can help with this code. I am sure it is something simple. It works fine until the last line the (x1down) line. I am not sure what I am missing there. I got that code by recording a macro in excel. It simulates the shift/end/down keystroke which will select all fields that are in the same condition (blank or containing data) as the cell you start at. If I copy the exact same code to excel directly, it works fine.

The error I get is runtime error '424' - object required


Anyway...hope you can help. Thanks.

Code:
Dim opensheet As Object

Set opensheet = GetObject("\\Netstore\trainingdocs\Rob\Data\open.xls")

With opensheet
.Application.Visible = True
.Parent.Windows(1).Visible = True
.Application.sheets("sheet1").Select
.Application.range("g2").Select
.Application.activecell.NumberFormat = "0"
.Application.activecell = 1
.Application.activecell.Copy
.Application.range("A2").Select
.Application.range(Selection, Selection.End(xlDown)).Select

 

[FlyerMike]

Have you tried dots (.) before the two instances of Selection?

Or perhaps change Selection to .Application.Selection?

 

[Robgould]

good idea, but no luck

This code

.Application.range(.Selection, .Selection.End(xlDown)).Select

gives the error : object dosen't support this property or method.

and this line

.Application.range(Application.Selection, Application.Selection.End(xlDown)).Select

Won't even run...says name can not result in the name of a macro.


Even this line won't work

.Application.Selection.End(xlDown).Select

It gives - application defined or object defined error.

could it have something to do with the word "end"?

I have been playing with this, but nothing at all seems to work that has that word end in it.

 

[FlyerMike]

Have you tried the selection in multiple steps (as opposed to a single, concise line of code)?

Dim sRange as String


....

sRange = "(A2:"
.Application.Selection.End(xlDown).Select
sRange = sRange & .Application.ActiveCell.Address & ")"
.Application.Range(sRange).Select

 

[Robgould]

wrong question?

It has just occured to me that maybe I am asking the wrong question. Let me tell you what I am trying to do. I have a spreadsheet that I have linked to an access 2000 database. My spreadsheet is an ouptut of work orders in our SAP plan maintenance module.

Every day, I go get the list from SAP and export it to this spreadsheet. When I to the file is overwritten, so I can't have any code attached to that workbook.

So I update this sheet, the I have to open the sheet and manipulate the data a bit. The number gets exported from SAP as text. I need it to be a number format. So I go into the workbook, change the format of a cell to number, make that cell = 1, copy that cell, and then paste special (multiply) the data in the number column. This makes my numbers numbers.


Then I close it and to access where I use an append query to add the data to my main table. And an update query to make sure that the rest of my data matches what is in SAP.

This is a bit cumbersome. I am trying to write code that will do this for me.

I will still manually export the data from SAP.

Then I want to go to access and click a button that will oen and modify the spreadsheet, then run my queries.

The reason that I am using the End(x1) down is to selct the range for my paste special operation.

I do not know how many rows are in my spreadsheet, except that it is over 12,000. So I want to select the cell in the top row, then tell it to go till the end of data in that column. This would be my range.

Am I going about this all wrong? This seemed to be straight forward until I ran into the snag with this code.

Thanks for your help.

 

[Robgould]

Have you tried the selection in multiple steps (as opposed to a single, concise line of code)?

Dim sRange as String


....

sRange = "(A2:"

sRange = sRange & .Application.ActiveCell.Address & ")"
.Application.Range(sRange).Select

I see what you are trying here, but the code still hangs for me. It gets stuck on .Application.Selection.End(xlDown).Select. It says "application defined or object defined error."

 

[Robgould]

This is how the whole code looks now.

Private Sub Refresh_Click()

Dim opensheet As Object
Dim sRange As String



Set opensheet = GetObject("\\Netstore\trainingdocs\Rob\Data\open.xls")

With opensheet
    .Application.Visible = True
    .Parent.Windows(1).Visible = True
    .Application.sheets("sheet1").Select
    .Application.range("g2").Select
    .Application.ActiveCell.NumberFormat = "0"
    .Application.ActiveCell = 1
    .Application.ActiveCell.copy
    .Application.range("a2").Select
    sRange = "(A2:"
    .Application.Selection.End(xlDown).Select
    sRange = sRange & .Application.ActiveCell.Address
    .Application.range(sRange).Select
End With

End Sub

 

[Bat17]

as you are not referencing Excel then Access won't know the value of 'xlDown'
try
.Application.range(.Selection, .Selection.End(-4121)).Select

HTH

Peter

 

[Robgould]

I just tried that and still no luck. When I do it that way I get object dosen't support this property or method. This is driving me crazy. I spent hours last night searching for an answer. At first I thought it must be a simple syntax issue, but now I'm not so sure. Is there some sort of limit to what I am able to get excel to do from access?

 

[Robgould]

tried to simplify

ok, I've tried to simplify the code piece by piece to find out exactly where the error is coming from.

This works fine.

.Application.range("a2", "a10").Select

This won't work

.Application.range(selection, "a10").Select

nor will this

.Application.range(.selection, "a10").Select

or this

.Application.range(application.selection, "a10").Select

I am sure it is something to do with the word selection

However in the same block of code, this works.

.Application.selection = 1

 

[Robgould]

more

ok....more trying

This works

.Application.range(.Application.selection, "a10").Select

This does not

.Application.range(.Application.selection, .Application.selection.End(x1down)).Select

 

[Robgould]

more trouble

I decided to skip this for now, until I could figure it out and just add more than I needed. Select like the 20000 rows and move on until I could look at this more. I had trouble with my very next line of code too. Here it is

.

Application.selection.pasteSpecial Paste:=xlAll, Operation:=xlMultiply

At this point I am really having a bad day. Hope you can help.

 

[Robgould]

first part works now

I finally got the first part working. Here it is.

.Application.range(.Application.range("a2"), .Application.range("a2").End(-4121)).Select

Still working on the second part.

 

[Robgould]

second part

This fixed the second part

.Selection.PasteSpecial Paste:=-4104, Operation:=4

 

[Robgould]

final

Here is the code that finally worked for me. Thanks for everyones help.

Private Sub Refresh_Click()

Dim MyStatusBar As Variant

vStatusBar = SysCmd(acSysCmdSetStatus, "Updateing Data....Please Wait")

Dim opensheet
Dim queryName As String
Set opensheet = CreateObject("Excel.Application")
opensheet.Visible = False
opensheet.Workbooks.Open "\\Netstore\trainingdocs\Rob\Data\open.xls"

With opensheet.Application
    .sheets("sheet1").SELECT
    .range("g2").SELECT
    .Selection.NumberFormat = "0"
    .Selection = 1
    .Selection.copy
    .range(.Application.range("a2"), .Application.range("a2").End(-4121)).SELECT
    .Selection.PasteSpecial Paste:=-4104, Operation:=4
    .range(.Application.range("e2"), .Application.range("e2").End(-4121)).SELECT
    .Selection.PasteSpecial Paste:=-4104, Operation:=4
    .Activeworkbook.Save
    .Activeworkbook.Close
    .Quit
End With

Set opensheet = CreateObject("Excel.Application")
opensheet.Visible = False
opensheet.Workbooks.Open "\\Netstore\trainingdocs\Rob\Data\complete.xls"

With opensheet.Application
    .sheets("sheet1").SELECT
    .range("g2").SELECT
    .Selection.NumberFormat = "0"
    .Selection = 1
    .Selection.copy
    .range(.Application.range("a2"), .Application.range("a2").End(-4121)).SELECT
    .Selection.PasteSpecial Paste:=-4104, Operation:=4
    .range(.Application.range("e2"), .Application.range("e2").End(-4121)).SELECT
    .Selection.PasteSpecial Paste:=-4104, Operation:=4
    .Activeworkbook.Save
    .Activeworkbook.Close
    .Quit
End With

Set opensheet = Nothing

queryName = "AppendOpen"
DoCmd.OpenQuery queryName, acViewNormal, acEdit

queryName = "deletecomplete"
DoCmd.OpenQuery queryName, acViewNormal, acEdit

queryName = "updateopen"
DoCmd.OpenQuery queryName, acViewNormal, acEdit

MyStatusBar = SysCmd(acSysCmdClearStatus)

End Sub

 

[Bat17]

Glad you have it sorted
Looks like you had a long day!

Peter