Clear Macro, and a Import Macro, How do I merge them

[santeria]

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:

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:

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.

 

[dtaylor]

Hi Santeria - try stepping through your code. Put the cursor at the beggining of your code and press F8 - this will highlight each line. Keep pressing F8 until it stops - this should be your error line.

Hey - I remember the wrestler you have pictured but can not remember his name ... could you enlighten me?


Dan

 

[santeria]

It's not a wrestler... It's Ace Frehley from KISS

Hey - I remember the wrestler you have pictured but can not remember his name ... could you enlighten me?


Dan

 

[dtaylor]

just spank me silly

 

[santeria]

Um, er...

Well, back to the Error...
Line 6 in the code as I have it...

.QueryTable.Delete

Is supposed to be the 1004 error.

Ta

just spank me silly

[ Is that supposed to be "Just spank me,silly"
Or ... ?? ]

 

[Smitty]

RE: Line 6 - Is that the first instance of .QueryTable.Delete in the code?

Smitty

 

[dtaylor]

Santeria - are you sure the querytable is present?

could ignore with On Error Resume Next ?????

just a stab at it---Dan

 

[santeria]

I F8'ed through the code, and as soon as I hit that line, it came up with the 1004 error
I'm totally confused...

Just checked, it is the first occurence of that code...

Odd things is that this messy code works:

Sub CLEARRAWDATASHEETSTEST()
'
' CLEARRAWDATASHEETSTEST Macro
' Macro recorded 1/22/2004 
'

'
    Sheets("RAW SKILL 77").Select
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.LargeScroll Down:=-2
    Selection.ClearContents
    Selection.QueryTable.Delete
    Sheets("RAW SKILL 17").Select
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll Down:=-2
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll Down:=-1
    ActiveWindow.LargeScroll ToRight:=-1
    Selection.ClearContents
    Selection.QueryTable.Delete
    Sheets("SKILL 77 SERV LEVEL").Select
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll Down:=-2
    ActiveWindow.LargeScroll ToRight:=-1
    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
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("SUMMARY").Select
End Sub

Ta

 

[Smitty]

Well, you can clean it one step at a time and try it:

Sub CLEARRAWDATASHEETSTEST() 
' 
' CLEARRAWDATASHEETSTEST Macro 
' Macro recorded 1/22/2004 
' 

' 
    Sheets("RAW SKILL 77").Range("A1:Z100").Select 
    Selection.ClearContents 
    Selection.QueryTable.Delete 
    Sheets("RAW SKILL 17").Range("A1:Z100").Select 
    Selection.ClearContents 
    Selection.QueryTable.Delete 
    Sheets("SKILL 77 SERV LEVEL").Range("A1:Z100").Select 
    Selection.ClearContents 
    Selection.QueryTable.Delete 
    Sheets("SKILL 17 SERV LEVEL").Range("A1:Z100").Select 
    Selection.ClearContents 
    Selection.QueryTable.Delete 
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
Sheets("SUMMARY").Select 
End Sub

If that works, try removing the selects.

HTH,

SMitty

 

[santeria]

Works Superbly.
Thanks.
How would I reference this in the import Macro?
Since, the Import Macro needs to be the over-arching Macro, and if this is a subset of that, then thats a great asset.

Ta

 

[Smitty]

Glad it works, now you can work on getting rid of the Selects.

In the Import macro, put this in the first line so it triggers before anything else: Application.Run "CLEARRAWDATASHEETSTEST"

Smitty