Excel Macro problem in Access

  • Thread starter Thread starter Sysdupe123
  • Start date Start date
S

Sysdupe123

I've got an Excel Macro in an Access Module and it isn't working properly.
The first time it is run (after the database is opened) it works perfectly.
Subsequent tries after that don't work right. The Excel code is below:

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.Select
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

The column and row deletions do not take place as it is supposed to. Any
help is appreciated.
 
Never mind! I got the answer on Tech-tips.com! Here is the code for
reference:

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
XLbase.ActiveSheet.Columns("A:A").EntireColumn.Delete Shift:=xlToLeft
Pause (2)
'Delete top 8 rows
XLbase.ActiveSheet.Rows("1:8").EntireRow.Delete Shift:=xlUp
Pause (2)
'copy paste values
XLbase.ActiveSheet.Cells.Select
Pause (2)
XLbase.Selection.Copy
Pause (4)
XLbase.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Pause (10)
'Delete unneeded columns at right
Pause (2)
XLbase.ActiveSheet.Columns("S:IV").Delete Shift:=xlToLeft
Pause (1)
Pause (1)
'sort to remove excess lines
XLbase.ActiveSheet.Cells.Sort Key1:=.ActiveSheet.Range("A1"), Order1:
=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
XLbase.ActiveSheet.Range("A3").Select
Pause (1)
XLbase.Selection.End(xlDown).Select
XLbase.Selection.Offset(1, 0).Select
Pause (1)
XLbase.Selection.EntireRow.Select
XLbase.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
 
Back
Top