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 There,
Not totally following on what you mean by"Could someone maybe advise on how to edit the clear sheet macro, "

but from a simplistic view and approach could you not create a sub that calls the clear macro then the import macro?

Sub DoIt()

Call CLEARRAWDATASHEETSTEST

Call doimPrtall

End Sub

Dan

 

[Smitty]

You don't really need to edit them together, just call them one after another after your Clear Data code runs:

Sub CLEARRAWDATASHEETSTEST()
    
    '   Need to define this sheet
    With Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Sheets("RAW SKILL 17").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Sheets("SKILL 77 SERV LEVEL").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Sheets("SKILL 17 SERV LEVEL").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Application
        .Run "doimPrt"
        .Run "doimPrtall"
    End With
    
End Sub

Of course, I didn't test any of this.

Smitty

 

[santeria]

Thanks. this edit seems better than my recorded one.
Also, can I call this macro from within the import Macro? And if so, whats the best place to put the call.

Ta

You don't really need to edit them together, just call them one after another after your Clear Data code runs

 

[Smitty]

can I call this macro from within the import Macro? And if so, whats the best place to put the call.

Sure.

Application.Run "CLEARRAWDATASHEETSTEST"

Since you mentioned that the data needed to be cleared first, I'd put that as the first line in your Import code.

Smitty

 

[santeria]

This errors with a 'Run time Error 1004'

Not clear what is happening.


I use the code slightly modified to test as:

Sub CLEARRAWDATASHEETSTEST2()
    
    '   Need to define this sheet
    With Sheets("RAW SKILL 77").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Sheets("RAW SKILL 17").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Sheets("SKILL 77 SERV LEVEL").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    With Sheets("SKILL 17 SERV LEVEL").Range("A1:Z100")
        .ClearContents
        .QueryTable.Delete
    End With
    
    
End Sub

Ta

 

[Smitty]

What line is it bombing on?

EDIT: It worked fine for me after I rem'd out the .QueryTable.Delete parts, as I don't have them to delete.

 

[santeria]

I redid that first macro, because the original one I did started bombing.
It came out as :

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

'
    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

And I have no idea what the difference is.

 

[Smitty]

And I have no idea what the difference is.

You don't need to "Select" ranges to work with them, and the ActiveWindow.LargeScroll can be eliminated as the recorder just recorded your movements. And wherever you see "Select" followed by "Selection", both words can be eliminated. The result is more concise code that also runs faster.

But you still didn't answer which line it was bombing on.

Smitty

 

[santeria]

Sorry,

The error never gave a line.
Just said 1004 as the error, it never even dropped to a VBA edit field like it usually does for me.