Posts by robertrobert905

Hi All

I've got a script which will create a pivot table and display the fields that I want.

Here is a sample code of what I have

Dim WSD As Worksheet
    Set WSD = Worksheets("SESSIONS PER USER - BY WEEK")
    Dim finalRow As Long    ' Find the last column with data
    Dim finalCol As Long    ' Find the range of the data
    finalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row            'finalRow and finalCol, will give me the range of data the pivot table needs
    finalCol = WSD.Cells(3, Columns.Count).End(xlToLeft).Column
    


   'Creates the pivot table    
   'Creates the pivot table    
    Sheets.Add.Name = "Table2"                               
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "SESSIONS PER USER - BY WEEK – R!R3C1:R" & finalRow & "C" & finalCol, Version:=xlPivotTableVersion10 _
        ).CreatePivotTable TableDestination:="Table2!R3C1", TableName:= _
        "PivotTable10", DefaultVersion:=xlPivotTableVersion10
    Sheets("Table2").Select
    
    'Selects the following fields to display
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Page")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Account")
        .Orientation = xlRowField
        .Position = 2
    End With
   
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("Page Views"), "Sum of Page Views", xlSum
    
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Page").AutoSort _
        xlDescending, "Sum of Page Views", ActiveSheet.PivotTables("PivotTable10"). _
        PivotColumnAxis.PivotLines(1), 1
    
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Username").AutoSort _
        xlDescending, "Sum of Page Views", ActiveSheet.PivotTables("PivotTable10"). _
        PivotColumnAxis.PivotLines(1), 1

Now I need to make a new pivot table, see screenshot. It should display account, and a sum for each of the weeks. However the "weeks" are not static. I'm hoping that there is a way for VBA to select ALL of the fields except the field Account Type,Name and Sum

[ATTACH=CONFIG]43651[/ATTACH]

Re: To Access A Website And Copy Table

Hi Kenneth, thank you for your help

I have found a very similar website, the only major difference is that this site does not require a password but I do not think that this is an issue

http://www.billboard.com/bbcom/index.jsp
and from that site you can select charts near the top left

This is the billboard music charts

Say for example I want excel to import the number one song of every chart

[Blocked Image: http://i25.tinypic.com/2bnvrd.jpg]

so if you look at the above image, that would be the drop down where you can select the different types of charts. So is there a way for vba to get me the number 1 single for each of these charts in the pic below

[Blocked Image: http://i32.tinypic.com/2j0cxhg.jpg]

Hi I'm doing some extremely repetitive work with a web tool at work. I need to import about 1800 tables for my first assignment and 5000 for my second.

I did a search on the forum and found out that Excel and vba is able to import tables from a website's chart which is amazing

I recorded a macro and got

Sheets("Sheet2").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL I can;t give out this information" _
        , Destination:=Range("A1"))
        .Name = "index"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "43"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet3").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL I can;t give out this information" _
        , Destination:=Range("A1"))
        .Name = "index"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "43"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

For each session I have to go to my site, log in with a username and password which is the same everytime then go to a specific page select the right drop down and import chart

[Blocked Image: http://i31.tinypic.com/2cdx576.jpg]

The Chart under the drop down is the chart I need to import.
The macro I recorded has no problem doing this but however it keeps grabbing the same chart, how do i fix that, so it knows to select the right drop down then import the right chart?

Hi i have 2 cells, one cell tells me the date and the other cell tells me the time.
IS there a way for VBA to add the two cells so it tells me the date AND time?

I tried recording a macro but it did not know how to add them, it just kind of pasted a static value. but the value i get are dynamic

[Blocked Image: http://i30.tinypic.com/120m3ow.jpg]

Re: Automatically Press Enter

hi thanks for the help every1, here is my code

Sheets("clusters").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\RobertR.Li\Desktop\June 5th 2008\macro\cm" & CStr(number) & "4.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(2, 9, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    
    Sheets("Slice groups").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\RobertR.Li\Desktop\June 5th 2008\macro\cm" & CStr(number) & "6.txt"
        .TextFilePlatform = 1251
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Nodegroups").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\RobertR.Li\Desktop\June 5th 2008\macro\cm" & CStr(number) & "12.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Slices").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\RobertR.Li\Desktop\June 5th 2008\macro\cm" & CStr(number) & "8.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("DNP").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\RobertR.Li\Desktop\June 5th 2008\macro\cm" & CStr(number) & "14.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 9, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Pipes").Select
    Range("E1").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\RobertR.Li\Desktop\June 5th 2008\macro\cm" & CStr(number) & "10.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

What this does is that it's importing 6 files, but every time it does so it asks the user to confirm which is not needed because everything is all set.

You could either press the key Import (the m character is underlined) or you could press cancel

Hi is there a code in vba that will allow it to automatically press the enter key?

my macro keeps prompting the user to open a file, but the file name is already given so all the user has to do is press enter. The macro will do this hundred of times so i think it would be much better if it can just press enter without human interaction.

Hi im trying to import a few text files into my excel template. the excel file will be a template so the settings are all set

What i need the macro to do is to go to Data -> Import External Data -> Edit Text Import

It would then select the approriate file. in this case for the dnp page it would select the text file that ends with the number 14 and import it.

Once in the import page [Blocked Image: http://i30.tinypic.com/33w04t2.jpg]

The program should just click next because it's already a template

and then at step 2

[Blocked Image: http://i28.tinypic.com/zirfvm.jpg] , the macro should just click finish.

so is there a way to have a macro do this?

Re: Import Blocks From Text File Into Seperate Workbooks

thank you,

Re: Determine Size Of Text Files

well, this is going to be part of another macro which i am going to write, so i would like the macro to do it automatically, besides i have a lot of files that the macro need to work on, so i can't imagine how many files there will be with nothing in them.

Re: Delete Txt Files

thanks, is there a way to loop through all the files in a folder to check for their sizes?

Hi , I'm trying to have excel VBA delete some txt files in a folder which have a size of 0kb.

My excel marco right now can create some important txt files for me from a mother txt file. The problem is, half of the TXT files that it ends up making are 0kb and are useless. The text files have 13 or 15 or 17 or 19 or 111 or 113 or or 115 or 116 or 117 or 118 in the file names

so say if i wanted the macro to do the file called cm1.txt i would get the files cm13.txt, cm15.txt etc with nothing in them. If the files to be edited was called cm3.txt, then macro would make files called cm33.txt, cm35.txt etc which have nothing in them. I would like these files to be automatically deleted.

Is there a way to do this?

Re: Import Blocks From Text File Into Seperate Workbooks

here is the url to the txt file http://www.mediafire.com/?3mdxi2mhiyy[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]and here is the link to the excel file with the macro http://www.mediafire.com/?nltdyvnzjxn

Re: Import Blocks From Text File Into Seperate Workbooks

well here is a typical file the first few lines are supposed to be ignored and we only need everything starting from [cluster]
i put it in a rar b/c it's too large, so you will have to extract it

Re: Import Blocks From Text File Into Seperate Workbooks

hi first of all thanks for your help, it works well and i'm just trying to make it exactly the way i want it

so this is the code i am using for parsing , ive edited a little customization

Sub ParseMasterTXTFile()
    Dim fn As Integer, i As Integer, sBuf1 As String, sBuf2 As String, sBasePath As String
     
    sBasePath = ActiveWorkbook.Path & "\" & "cm1" [B]'i edited this part b/c the file is called cm1[/B]


    sBuf1 = ""
    sBuf2 = ""
    i = 0
    fn = FreeFile
     
    Open sBasePath & ".txt" For Input As #fn
    Do While Not EOF(fn)
        Line Input #fn, sBuf1
        If sBuf1 <> "" Then
            sBuf2 = sBuf2 & sBuf1 & vbCrLf
        Else
            i = i + 1
            SaveTextFile sBasePath & CStr(i) & ".txt", sBuf2, True
            sBuf2 = ""
        End If
     Loop
     
End Sub
 
 
 Function SaveTextFile(strFile As String, strData As String, Optional bOverWrite As Boolean = False) As Boolean
    Dim iHandle As Integer, l As Long
    If Not bOverWrite Then
        If Len(Dir(strFile)) > 0 Then
            SaveTextFile = False
            Exit Function
        End If
    End If
    iHandle = FreeFile
    l = Len(strData)
    Open strFile For Binary Access Write As #iHandle Len = 2  [B]'changed from 1 to 2 b/c the space between sections is 2 lines[/B]


    Put #iHandle, , strData
    Close #iHandle
    SaveTextFile = True
End Function

So i ran the program and what happened was

i got these TXT files in my folder

cm11 2kb
cm12 0kb
cm13 7kb
cm14 0kb
cm15 52kb
cm16 0kb
cm110 0kb
cm111 67kb
cm112 0kb
cm113 0kb
cm114 0kb
cm115 0kb
cm17 625kb
cm18 0kb
cm19 50kb

the files with actualy sizes were the files that i needed and are perfect, but i don't understand why there are so many files with 0 kb and nothing in them. Do you know how I can get rid of them?

Re: Import Blocks From Text File Into Seperate Workbooks

thanks kenneth, can you explain to me this line
Open strFile For Binary Access Write As #iHandle Len = l
what does that line do? i got an error on this line when i tried to parse my file called sample with

[cluster]
itvelement = 2 34839 0 0 0 100 2
itvelement = 4 36203 0 0 0 100 2
itvelement = 5 36204 0 0 0 100 2
itvelement = 6 36662 0 0 0 100 2
itvelement = 7 36664 0 0 0 100 2

[slicegroup]
itvelement = 25 5 0 0 0 0
itvelement = 40 0 0 0 0 0
itvelement = 41 1 0 0 0 0
itvelement = 42 2 0 0 0 0

[slice]
itvelement = 250 1310720 0 0 0 0 0
itvelement = 251 1310721 0 0 0 0 0
itvelement = 252 1310722 0 0 0 0 0
itvelement = 253 1310723 0 0 0 0 0

[pipe]
dnelement = 1000 13600 PB002-VQ001_RFOut1 38800 0 131093 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1001 13601 PB002-VQ001_RFOut2 38800 0 2752573 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1002 13602 PB002-VQ001_RFOut3 38800 0 5374053 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1003 13603 PB002-VQ001_RFOut4 38800 0 7995533 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1004 13604 PB002-VQ002_RFOut1 38800 0 131085 621000000 256 13001 undefined 0 262144 8 0 00:14:1B:0D:F7:71 <unnamed> 0 0
dnelement = 1005 13605 PB002-VQ002_RFOut2 38800 0 1310751 621000000 256 13001 undefined 0 262144 8 0

[nodegroup]
dnelement = 1001 205 PB002-SG001 0 100 0 PB002-
dnelement = 1002 206 PB002-SG002 0 100 0 PB002-
dnelement = 1003 253 PB002-SG003 0 100 0 PB002-
dnelement = 4114 837 PM001-SG114 0 100 0 PM001-
dnelement = 4115 838 PM001-SG115 0 100 0 PM001-
dnelement = 4139 921 PM001-SG139 0 100 0 PM001-

[dnp]
dnp = 1079 11 111 1115 4284 4033
dnp = 1080 11 111 1115 4332 4081
dnp = 1081 11 111 1115 4472 4121
dnp = 1082 11 111 1115 4516 4133
dnp = 1083 11 111 1115 4551 4144

it made the first text file called sample1 and placed all the cluster entries in it , then it just stopped and gave me an error on that line

Re: Pull Text File, Format,and Save As Many Seprate Files

something like that, only the sections will all be in one place, so you don't have to search

Section1
test1
test2
testa
testb

Section2
test1
test5

and
TXTfile1:
Section1
test1
test2
testa
testb

TXT file2:
Section2
test1
test5[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]here is an example of the text file, i have cleaned it up to give u a general idea, but each section in actuality would be much longer, like thousands of entries

[cluster]
itvelement = 2 34839 0 0 0 100 2
itvelement = 4 36203 0 0 0 100 2
itvelement = 5 36204 0 0 0 100 2
itvelement = 6 36662 0 0 0 100 2
itvelement = 7 36664 0 0 0 100 2

[slicegroup]
itvelement = 25 5 0 0 0 0
itvelement = 40 0 0 0 0 0
itvelement = 41 1 0 0 0 0
itvelement = 42 2 0 0 0 0

[slice]
itvelement = 250 1310720 0 0 0 0 0
itvelement = 251 1310721 0 0 0 0 0
itvelement = 252 1310722 0 0 0 0 0
itvelement = 253 1310723 0 0 0 0 0

[pipe]
dnelement = 1000 13600 PB002-VQ001_RFOut1 38800 0 131093 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1001 13601 PB002-VQ001_RFOut2 38800 0 2752573 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1002 13602 PB002-VQ001_RFOut3 38800 0 5374053 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1003 13603 PB002-VQ001_RFOut4 38800 0 7995533 609000000 256 0 undefined 0 327680 5 0 1.2.3.4 AA:BB:CC:DD:EE:FF <unnamed> 0 0
dnelement = 1004 13604 PB002-VQ002_RFOut1 38800 0 131085 621000000 256 13001 undefined 0 262144 8 0 00:14:1B:0D:F7:71 <unnamed> 0 0
dnelement = 1005 13605 PB002-VQ002_RFOut2 38800 0 1310751 621000000 256 13001 undefined 0 262144 8 0

[nodegroup]
dnelement = 1001 205 PB002-SG001 0 100 0 PB002-
dnelement = 1002 206 PB002-SG002 0 100 0 PB002-
dnelement = 1003 253 PB002-SG003 0 100 0 PB002-
dnelement = 4114 837 PM001-SG114 0 100 0 PM001-
dnelement = 4115 838 PM001-SG115 0 100 0 PM001-
dnelement = 4139 921 PM001-SG139 0 100 0 PM001-

[dnp]
dnp = 1079 11 111 1115 4284 4033
dnp = 1080 11 111 1115 4332 4081
dnp = 1081 11 111 1115 4472 4121
dnp = 1082 11 111 1115 4516 4133
dnp = 1083 11 111 1115 4551 4144

Re: Pull Text File, Format,and Save As Many Seprate Files

Hello, my main problem is not the importing part. Sorry if i was not being clear, my main problem is that I need to use this original text file and save them into 6 separate files, each file will have a separate section of the original text file. To identify a section is this text file, each section has a title. So I would need to copy that section and paste it in a new file and save.

Hi

I have wrote a few macros before which involved searching, copy pasting and comparing.

I now need a macro that can first open a text file, in this text file will be 6 section separated by 6 different titles so it will look something like this

title1
467575
56
56745
547
575

title2
65ryret
ryr534

title3
sdgsdfg
hfghf
hfdgh

and so on

I need to copy each section into a new text file and save it and then import each of those new text files in excel, which I probably won't have trouble doing. So my question is how can vba macro edit this text file for me?

PS, the original text file will have a set name and all the titles have a set name as well. the real text files will have up to 10000 lines, so my example is just a simplified representation.