New Posts | My Posts


Link me to ...


fatty
08-05-2010, 16:46

Magic Bum

Posts: 603
Joined: Jun 2008
... to some list of commands and their meanings used in the programs VBA (Microsoft Office (Excel) macro tools) and BASIC, which i heard are pretty similar programming softwares. thank you very much

a great day for freedom
<!-- m --><a class="postlink" href="http://www.youtube.com/user/BCThps3">http://www.youtube.com/user/BCThps3</a><!-- m -->
Quote this message in a reply


Lyst
08-06-2010, 08:52

Administrator
| Website
Posts: 2,375
Joined: Jun 2008
Just look for "*language* + syntax"
You'll need to google it yourself, because I get a lot of dutch results.

Yes, but why is the rum gone?
Quote this message in a reply


fatty
08-06-2010, 18:41

Magic Bum

Posts: 603
Joined: Jun 2008
im supposed to make a macro for my work, which would open all excel files in a desired folder with names made out of "ES" and a few numbers that grow depending on the date created, and copy a specific value on a specific sheet, that isnt always on the same place, it moves around +-3 rows, columns, depending on the file, and the macro would update itself automatically when new entries in the folder would be made. and i was introduced to macros 2 days ago lol

a great day for freedom
<!-- m --><a class="postlink" href="http://www.youtube.com/user/BCThps3">http://www.youtube.com/user/BCThps3</a><!-- m -->
Quote this message in a reply


foolish
08-07-2010, 02:12

Three-sixty!

Posts: 409
Joined: Sep 2008
This seems to be a pretty hard job for someone who was introduced to VBA 2 days ago. Whatever I have worked a bit with VBA and I found a code and added some stuff. This might help you, I don't know. I haven't tested the code either, so you'll have to check if it works.

Code:
Sub fileloop()
    Dim MyDir As String
    Dim strPath As String
    Dim strFileName As String

    MyDir = ActiveWorkbook.Path ' current path
    strPath = MyDir & "\files" ' files subdir

    With Application.FileSearch
        .NewSearch
        .LookIn = strPath
        .SearchSubFolders = False
        .Filename = ".xls"

        If .Execute > 0 Then

            For Each strFileName In .FoundFiles

                If InStr(1, strFileName, "ES") <> 0 Then

                Workbooks.Open strFileName

                    With ActiveWorkbook
                        .Worksheets("Sheet1").Cells(1, 1).Value = "Hello"
                        .Save
                        .Close
                    End With

                End If
            Next
        End If
    End With
End Sub

This should simply be a loop on searching files (strFileName) in a specific folder (strPath), check if the filename contains "ES" and add a value to a cell in each file.

oh noes!
Quote this message in a reply


fatty
08-08-2010, 12:03

Magic Bum

Posts: 603
Joined: Jun 2008
thats some cool stuff right there, i cant test it here, cause i dont have excel, ill test it tomorrow at work. really my problem for now is, i dont know exactly how does everything work yet, i dont know what all those strings mean, the syntax commands, the "$" and the functions, like LTrim$ and so on... anyway, thank you very much!

a great day for freedom
<!-- m --><a class="postlink" href="http://www.youtube.com/user/BCThps3">http://www.youtube.com/user/BCThps3</a><!-- m -->
Quote this message in a reply


foolish
08-08-2010, 12:35

Three-sixty!

Posts: 409
Joined: Sep 2008
Yeah I thought so. I'm not working much with VBA, but for quite some time now and I totally wouldn't except that to be a task for someone who's new to VBA. I'm not sure if the code works either, since I didn't test it, this seemed like a simple way to solve it to me, but usually it never is Xd

The "strings" in the code are variables with text values, which are simply called String type.

Maybe you should google for stuff like "VBA tutorial", so you'll become familiar with the basics and will be able to understand the code better.

oh noes!
Quote this message in a reply


fatty
08-11-2010, 18:08

Magic Bum

Posts: 603
Joined: Jun 2008
this loop you have written was the base for 2 macros now, edited it and added a few things, here's one of them
Code:
Sub fileloop()
Application.DisplayAlerts = False

   Dim MyDir As String
   Dim strPath As String
   Dim strFileName As Variant
    Range("A1") = "vzorec"
    Range("B1") = "CYC"
    Range("C1") = "POS"
    Range("E1") = "datum Å¡tetja"
    Range("F1") = "SQP"
    Range("G1") = "ID"
    Range("N1") = "cpm"
    Range("O1") = "cpm err"
    Range("P1") = "cpm average"
    Range("Q1") = "stdev cpm"
    Range("R1") = "ratio"
    Range("S1") = "datum Å¡tetja"
    Range("T1") = "datum err."
    Range("U1") = "average cpm"
    Range("V1") = "stdev cpm"
    Range("W1") = "datum Å¡tetja"
    Range("X1") = "datum err"
    
   MyDir = ActiveWorkbook.Path ' current path
   strPath = "C:\Documents and Settings\Miha\My Documents\M&M\Kontrolne karte\Podatki\Izracun EXX\xls" ' files subdir

   With Application.FileSearch
      .NewSearch
      .LookIn = strPath
      .SearchSubFolders = False
      .Filename = ".xls"

      If .Execute > 0 Then
Dim a As Integer
            a = 2
         For Each strFileName In .FoundFiles

            If InStr(1, strFileName, "IZRACUN") <> 0 Then

            Workbooks.Open Filename:=strFileName, UpdateLinks:=3
            Application.DisplayAlerts = False
            aind$ = LTrim$(Str$(a))
               With ActiveWorkbook
                  .Worksheets("LSC data-kontrolni").Select
                  Range("A2:X31").Select
                  Selection.Copy
                  
                  Windows("kontrolna.xls").Activate
                  Worksheets("Sheet1").Select
                  Range("A" + aind$).Select
                  Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                  .Close
              End With
            a = a + 30
              

            End If
         Next
      End If
   End With
End Sub
it saved me probably a few days of constant: open file>copy>paste in another file>close file>open new file>....

a great day for freedom
<!-- m --><a class="postlink" href="http://www.youtube.com/user/BCThps3">http://www.youtube.com/user/BCThps3</a><!-- m -->
Quote this message in a reply


foolish
08-13-2010, 11:34

Three-sixty!

Posts: 409
Joined: Sep 2008
Nice that you could use part of my code, seems I made a few mistakes, but you were able to get rid of them Tongue Gratz, your code looks pretty good to me Original

oh noes!
Quote this message in a reply



Forum Jump:

User(s) browsing this thread:
1 Guest(s)
Contact Us | Chapter 3 | Lite (Archive) Mode | RSS Syndication Return to top