Excel Macros in Access 1

[SysDupe123]

I've got an Excel Macro in Access that works once and then gives an error or does not perform correctly afterwards. I close the database and re-open it and it works fine the first time only again.
Here is the Excel part of the code. I'm trimming a part of an Excel file and importing it into Access.
Set XLbase = New Excel.Application
Set XLNwSt = XLbase.Workbooks.Add
Set XLSheet = XLbase.Workbooks.Open(MatrixFile)
XLSheet.ActiveSheet.Copy XLNwSt.ActiveSheet
With XLNwSt
'delete first column
Range("A1").Activate
Columns("A:A").Select
Pause (2)
Selection.Delete Shift:=xlToLeft
'copy paste values
Cells.Select
Pause (2)
Selection.Copy
Pause (4)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Delete top 8 rows
Pause (10)
Rows("1:8").Select
Pause (2)
Selection.Delete Shift:=xlUp
Pause (2)
'Delete unneeded columns at right
ActiveWindow.SmallScroll ToRight:=6
Pause (2)
Columns("S:S").Select
Pause (1)
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Pause (1)
Selection.Delete Shift:=xlToLeft
'sort to remove excess lines
Cells.Select
Pause (1)
Range("A2").Activate
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select
Pause (1)
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Pause (1)
Selection.EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
End With
XLSheet.Close False
Set XLSheet = Nothing
XLNwSt.SaveAs NwMatrixFile & ".txt", xlTextMSDOS
XLNwSt.Close True
XLbase.Quit
Set XLNwSt = Nothing
Set XLbase = Nothing


Any ideas on how to get it working every time? I'm getting a 1004 Error.

 

[PHV]

You have to qualify all the excel objects you manipulate:
With XLNwSt
'delete first column
[!].[/!]Range("A1").Activate
[!].[/!]Columns("A:A").Select
Pause (2)
[!]XLbase[/!]Selection.Delete Shift:=xlToLeft
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
XLbase[!].[/!]Selection.Delete Shift:=xlToLeft

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

Thanks PHV for that, I've gotten rid of the Error message. My other problem still around, it doesn't perform the actions requested. The column delete doesn't happen and the row delete doesn't happen. This is the most critical, as when I import, without getting rid of the first column, it imports everything wrong!

 

[PHV]

And what is your new actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

Here is the new code:
Set XLbase = New Excel.Application
Set XLNwSt = XLbase.Workbooks.Add
Set XLSheet = XLbase.Workbooks.Open(MatrixFile)
XLSheet.ActiveSheet.Copy XLNwSt.ActiveSheet
With XLNwSt
'delete first column
Range("A1").Activate
Columns("A:A").Select
Pause (2)
XLbase.Selection.Delete Shift:=xlToLeft
'copy paste values
Cells.Select
Pause (2)
XLbase.Selection.Copy
Pause (4)
XLbase.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Delete top 8 rows
Pause (10)
Rows("1:8").Select
Pause (2)
XLbase.Selection.Delete Shift:=xlUp
Pause (2)
'Delete unneeded columns at right
XLbase.ActiveWindow.SmallScroll ToRight:=6
Pause (2)
Columns("S:S").Select
Pause (1)
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Pause (1)
XLbase.Selection.Delete Shift:=xlToLeft
'sort to remove excess lines
Cells.Select
Pause (1)
Range("A2").Activate
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select
Pause (1)
XLbase.Selection.End(xlDown).Select
XLbase.Selection.Offset(1, 0).Select
Pause (1)
XLbase.Selection.EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
XLbase.Selection.Delete Shift:=xlUp
End With
XLSheet.Close False
Set XLSheet = Nothing
XLNwSt.SaveAs NwMatrixFile & ".txt", xlTextMSDOS
XLNwSt.Close True
XLbase.Quit
Set XLNwSt = Nothing
Set XLbase = Nothing

 

[bubba100]

In Excel the following will work
Range("A1").Activate

But from Access to control Excel it won't. Try
.Range("A1").Activate

 

[SysDupe123]

Sorry,
.Range("A1").Activate doesn't work.

It's not supported

 

[bubba100]

XLBase.worksheets("yourSheetName").range("A1").select should work.

 

[SysDupe123]

XLBase.worksheets isn't an option. XLBase.Range is but that didn't work either.

 

[PHV]

And what about this (typed, untested)?
Set XLbase = New Excel.Application
Set XLNwSt = XLbase.Workbooks.Add
Set XLSheet = XLbase.Workbooks.Open(MatrixFile)
XLSheet.ActiveSheet.Copy XLNwSt.ActiveSheet
With XLNwSt
'delete first column
.ActiveSheet.Columns("A:A").Delete Shift:=xlToLeft
'copy paste values
.ActiveSheet.Cells.Select
Pause (2)
XLbase.Selection.Copy
Pause (4)
XLbase.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Delete top 8 rows
Pause (10)
.ActiveSheet.Rows("1:8").Delete Shift:=xlUp
Pause (2)
'Delete unneeded columns at right
XLbase.ActiveWindow.SmallScroll ToRight:=6
Pause (2)
.ActiveSheet.Columns("S:S").Select
Pause (1)
.ActiveSheet.Range(XLbase.Selection, XLbase.Selection.End(xlToRight)).Delete Shift:=xlToLeft
'sort to remove excess lines
.ActiveSheet.Cells.Sort Key1:=.ActiveSheet.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.ActiveSheet.Range("A3").Select
Pause (1)
XLbase.Selection.End(xlDown).Select
XLbase.Selection.Offset(1, 0).Select
Pause (1)
XLbase.Selection.EntireRow.Select
.ActiveSheet.Range(XLbase.Selection, XLbase.Selection.End(xlDown)).Delete Shift:=xlUp
End With
XLSheet.Close False
Set XLSheet = Nothing
XLNwSt.SaveAs NwMatrixFile & ".txt", xlTextMSDOS
XLNwSt.Close True
XLbase.Quit
Set XLNwSt = Nothing
Set XLbase = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

Thanks, but the line

.ActiveSheet.Range(XLbase.Selection, XLbase.Selection.End(xlToRight)).Delete Shift:=xlToLeft

gave an application error, but when I took out the XLBase part, it removed the error.

It still didn't do the deletions, though.

 

[PHV]

what about replacing this:
.ActiveSheet.Columns("S:S").Select
Pause (1)
.ActiveSheet.Range(XLbase.Selection, XLbase.Selection.End(xlToRight)).Delete Shift:=xlToLeft
With this ?
.ActiveSheet.Columns("S:IV").Delete Shift:=xlToLeft

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

No error messages, but still not working right. Column ("A:A") and Row("1:8") are still not deleting and the re-sort isn't happening. Those are the most critical parts, in that order.

 

[PHV]

you may try this:
.ActiveSheet.Columns("A:A").EntireColumn.Delete Shift:=xlToLeft
and this:
.ActiveSheet.Rows("1:8").EntireRow.Delete Shift:=xlUp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

Tried that, didn't work. For some reason, it doesn't want to execute those commands.

 

[SysDupe123]

Thank you, PHV!!!! I saved and closed my database, re-opened it and it worked great! I've tried it out a few times now and it didn't even hiccup! Thanks a million!