Macro - Some Adjustment Needed

[Vgabond]

Hi ALL

I'm recorded a macro and manage to use but my challenge is that I have to keep changing the file name in order to make it run ( for different file). Below is the code and the one that highlighted in RED is the file name which I have to change everytime I run a new file. Any help is much appreciated. Thanks

:

Sub Submit()
'
' Submit Macro
' Macro recorded 21/03/2011 by Vgabond
'

'
    Rows("1:1").Select
    Range("AA1").Activate
    Selection.Find(What:="submit", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Columns("BC:BC").Select
    Selection.TextToColumns Destination:=Range("BC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 4), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True
    Range("A1:CW3729").Select
    Range("AX7").Activate
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "[COLOR=Red][B]Submission_23032011[/B][/COLOR]!R1C1:R3729C101").CreatePivotTable TableDestination:="", TableName _
        :="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Submit")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("TelNum"), "Count of TelNum", xlCount
    ActiveWorkbook.Save
End Sub

 

[Vgabond]

Anyone?

 

[ColinKJ]

Hi Vgabond,

You could try adding an Input Box requesting the file name when the file is run:

UN:TESTED:

Extract:

Range("A1:CW3729").Select
    Range("AX7").Activate
    FileName1 = InputBox("Enter File Name")
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        FileName1 & "!R1C1:R3729C101").CreatePivotTable TableDestination:="", TableName _
        :="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

 

[Vgabond]

Hi Vgabond,

You could try adding an Input Box requesting the file name when the file is run:

UN:TESTED:

Extract:

Range("A1:CW3729").Select
    Range("AX7").Activate
    FileName1 = InputBox("Enter File Name")
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        FileName1 & "!R1C1:R3729C101").CreatePivotTable TableDestination:="", TableName _
        :="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

Hi Colin

Thanks. It works but is it possible that it can be run without asking the file name ?

 

[ColinKJ]

Hi Vgabond,

Looking at the File name, it appears to be based on a date, when you run the macro, is the filename based on the current day date?.

If not, where can the file name be found.

 

[Vgabond]

Hi Colin
The filename can be any name. The default filename when i download the raw data from the source is only "Filename". I just changed to submission_date as I will keep the file for a month for audit purposes.

 

[ColinKJ]

Hi Vgabond,

I'm not that familiar with

xlDatabase, SourceData

Is your file Submission_XXXXXXXX a file on a disc, is it a file that is open when the macro is run, or is it a Tab in the active Workbook.

I'm just trying to establish where we can pick up the current file name.

 

[Vgabond]

Hi Vgabond,

I'm not that familiar with

xlDatabase, SourceData

Is your file Submission_XXXXXXXX a file on a disc, is it a file that is open when the macro is run, or is it a Tab in the active Workbook.

I'm just trying to establish where we can pick up the current file name.

It's file on a disc but the file name can be varies. Or maybe instead of I enter the file name can I browse the file name then?

 

[ColinKJ]

Vgabond,

What file type / extention is it, and, is it always in the same folder. If it is in the same folder, what is the directory Path.

 

[Vgabond]

Vgabond,

What file type / extention is it, and, is it always in the same folder. If it is in the same folder, what is the directory Path.

It's .xls and yes it will always in the same folder.
The directory path = C:\File\