santeria
Well-known Member
- Joined
- Oct 7, 2003
- Messages
- 1,844
I have a Recorded Macro that clears some data pages, and I have an import macro that needs to have the Clear Page Macro edited into it.
I am not clear how to both edit the Clear Sheet Macro, nor how to edit the two together.
This is the recorded Clear Sheet Macro:
And here is the import Macro:
Could someone maybe advise on how to edit the clear sheet macro, and how the Import macro can be edited to bring in the clear sheet macro, so that the pages are cleared before the data is imported.
Ta Muchly.
I am not clear how to both edit the Clear Sheet Macro, nor how to edit the two together.
This is the recorded Clear Sheet Macro:
Code:
Sub CLEARRAWDATASHEETSTEST()
'
' CLEARRAWDATASHEETSTEST Macro
' Macro recorded 1/22/2004 '
'
Range("A1:Z100").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.LargeScroll Down:=-2
Selection.ClearContents
Selection.QueryTable.Delete
Range("I21").Select
Sheets("RAW SKILL 17").Select
Range("A1:Z100").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.LargeScroll Down:=-2
Selection.ClearContents
Selection.QueryTable.Delete
Range("C7").Select
Sheets("SKILL 77 SERV LEVEL").Select
Range("A1:Z100").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.LargeScroll Down:=-2
Selection.ClearContents
Selection.QueryTable.Delete
Sheets("SKILL 17 SERV LEVEL").Select
Range("A1:Z100").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.LargeScroll Down:=-2
Selection.ClearContents
Selection.QueryTable.Delete
Range("B23").Select
End Sub
And here is the import Macro:
Code:
Sub doimPrt(ByVal fName As String, ByVal shtNme As String, ByVal destName As String)
Dim myDir As String
myDir = "C:\EXPORTS\" & Format$(Date, "mm_mmmm") & "\CMS\DAILY\"
With Sheets(shtNme)
With .QueryTables.Add(Connection:="TEXT;" & myDir & fName, Destination:=.Range(destName))
.Name = TableName
.FieldNames = True
.PreserveFormatting = True
.TextFileParseType = xlDelimited 'not sure if you need this line
.TextFileTextQualifier = xlTextQualifierDoubleQuote 'not sure if you need this line
.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = False
.TextFileTabDelimiter = True
.TextFileColumnDataTypes = Array(1, 1)
.Refresh BackgroundQuery:=False
End With
End With
End Sub
Sub doimPrtall()
Dim fNames As Variant, shtNmes As Variant, DestNames As Variant, i As Long
fNames = Array("1.txt", "2.txt", "3.txt", "4.txt")
shtNmes = Array("RAW SKILL 77", "RAW SKILL 17", "SKILL 77 SERV LEVEL", "SKILL 17 SERV LEVEL")
DestNames = "A1"
For i = LBound(fNames) To UBound(fNames)
doimPrt fNames(i), shtNmes(i), DestNames
Next
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("SUMMARY").Select
End Sub
Could someone maybe advise on how to edit the clear sheet macro, and how the Import macro can be edited to bring in the clear sheet macro, so that the pages are cleared before the data is imported.
Ta Muchly.