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

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:

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.

(y)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
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

Legend
Joined
May 15, 2003
Messages
29,536
You don't really need to edit them together, just call them one after another after your Clear Data code runs:
Code:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

(y)

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
This errors with a 'Run time Error 1004'

Not clear what is happening.


I use the code slightly modified to test as:

Code:
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

(y)
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
I redid that first macro, because the original one I did started bombing.
It came out as :

Code:
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.

:unsure:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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.

(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,857
Messages
5,544,694
Members
410,630
Latest member
Maggie28
Top