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)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,816
Messages
5,772,453
Members
425,759
Latest member
renoz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top