Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel macro's and VBA

  1. Jul 27, 2013 #1
    Hello all,

    A brief introduction to my skill set: I've taken an intro to C++ course which was geared toward Science/Math/Engineering students so I'm familiar with programming in general, conditions, loops, functions, pointers (or whatever its called when you call by ref/value etc), arrays (n dimensional) and even a bit of object oriented programming (we played with robots!), classes, objects etc. So I don't think you have to start from the very beginning with me but I'm no whiz at this stuff either and severely lack experience, I've written maybe 20 programs in C++ in my life.

    I'm learning how to make macros in Excel to be more efficient in some of the work that I do which is pretty labor intensive. In a short summary the task requires me to import data from a .dat file, eliminate some unwanted stuff at the start of the file, and fit an equation to the data to get coefficients. I also have multiple trials per workbook so the task is repeated n number of times until I have done all of my replicates. Then I go onto another workbook which is a different set of conditions and do the whole thing all over again. I learned some basic stuff about recording macro's and it has made life a lot easier already but I'm hoping to optimize it a bit more by writing in/editing some of the code. I know very little about VBA (just read some tutorials and basic stuff online) so I'm not sure if what I want to do is even possible.

    What the code does now is that it imports X and Y values into two columns (say A and B) from a .dat file that I put into a specific folder on my desktop. I haven't made the code graph a scatter plot or fit a line because each trial is somewhat unique and I'd rather do that myself. Now I looked at the code in the editor and saw that it always wants to put the data into column A, and if there is already data there it moves the old data over to make room. So if I've already run the macro once, there is data in A and B then, on the next run, it takes the original data and moves it to column C and D and imports the new data into column A and B. It is also wonky because it doesn't always resize the column length even though I did that during the recording. It seems to resize the first time the macro runs but leaves the columns wide on subsequent runs.

    What I would like it to do is look at column A and B and if there is data there I want the macro to skip column C and import into column D and E. In other words I'd like there to be one empty column between each trial and I'd like each subsequent trial to go into a later column. I guess something akin to an if/if else statement is necessary? But then I need to declare a variable to store the column letter/number and add an appropriate amount to get the "skipping" thing going properly. I'm not even sure if I can do that in VBA.

    Here is what I'm hoping to write the code to achieve (I guess this is pseudo-pseudo-code):

    1. look at column A, if there is data there then look at column B etc until you get to a column with a blank cell.
    2. When you hit a blank cell, check that the next cell has no data and if it doesn't then go import the data into that cell (the second blank cell), if it does then keep scanning the cells until you get to the condition of two consecutive empty cells.
    3. Import into the second empty cell as usual.

    It would also greatly help me if I could make some type of loop (while loop or do-while maybe?) which will import multiple .dat files in a specified folder. For example: I could just put in the 5 trials into a certain folder and run the macro once to get all five trials imported into one workbook. I have no idea how to set up an appropriate condition for that though, only thing I can think of is to try and number the files 1...n.dat and when the macro reaches the last number (say 5.dat) it will look for 6.dat, not find it, and end. That would require something like a counter I guess but I'm not sure I can do any of that kind of stuff in VBA (or any language for that matter).

    Thanks in advance for any help you guys can offer. I'm not looking for people to do my work for me and welcome some links to materials which will explain some of the functionality/syntax of VBA to me. I also apologize if I am butchering terms, my professor was keen on getting us to learn the fundamental concepts and not just words and syntax.
     
  2. jcsd
  3. Jul 27, 2013 #2

    phinds

    User Avatar
    Gold Member
    2016 Award

    Perhaps this will get you started

    Code (Text):

    Public Sub extra_raw1()
        Dim ix As Integer
        '
        ' find the first empty cell in column 2
        '
        ix = 1
        Do While True
            If Cells(ix, 2) = "" Then
                Exit Do
            Else
                ix = ix + 1
            End If
        Loop
        '
        ' ix now has the row index of the first empty cell in column 2
        '
    End Sub
     
     
  4. Jul 27, 2013 #3
    Thank you so much for trying to help me!

    Here is how I've tried to change your original code to fit what I want. Since I want it to check IE A1, then B1, then C1, I switched some stuff around and renamed the variables. I'm trying to get it to make sure that there are consecutive empty columns before it imports into "Cells(RowIndex, ColumnIndex)" but can't get the thing to work

    Code (Text):
        Dim RowIndex As Long
        Dim ColumnIndex As Long

        RowIndex = 1
        ColumnIndex = 1

        Do While True
            If Cells(RowIndex, ColumnIndex) = "" Then
                ColumnIndex = ColumnIndex + 1
                If Cells(RowIndex, ColumnIndex) = "" Then
                    Exit Do
                Else
                    ColumnIndex = ColumnIndex + 1
                End If
            End If
        Loop
    Later when it sets the destination for the data I have changed it like so:

    Code (Text):
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;C:\PATH\Data.dat", Destination:=Cells(RowIndex, ColumnIndex))
    This last bit of code is from my recording of the macro...I don't want to give the false impression that I actually can write that from scratch because it looks more complicated than the stuff I'm trying to do above.

    It works for a blank workbook but when I try and import a second .dat file it just sits there for a while then crashes. The syntax is tricky, C++ is easier with the parentheses lol.

    Also I can't figure out what the condition is for the do-while loop. There isn't a boolean variable or anything, how does it know when to stop the do-while loop? Can I just declare some type of bool and initialize to True then make it false somewhere later? Any help would be appreciated, I'm not a programmer but I love doing this stuff.

    EDIT: I've also tried the following and its not working either.

    Code (Text):
        Dim RowIndex As Long
        Dim ColumnIndex As Long
        Dim DoWhileCondition As Boolean

        RowIndex = 1
        ColumnIndex = 1
        DoWhileCondition = True

        Do While DoWhileCondition
            If Cells(RowIndex, ColumnIndex) = "" Then
                ColumnIndex = ColumnIndex + 1
                If Cells(RowIndex, ColumnIndex) = "" Then
                    DoWhileCondition = False
                Else
                    ColumnIndex = ColumnIndex + 1
                End If
            End If
        Loop
    ANOTHER EDIT: I'M AN IDIOT! I forgot to increase columnIndex by 1 in the outer most If-Else statement. Its working now. Here is the code that works,

    Code (Text):
        Dim RowIndex As Long
        Dim ColumnIndex As Long
        Dim DoWhileCondition As Boolean

        RowIndex = 1
        ColumnIndex = 1
        DoWhileCondition = True

        Do While DoWhileCondition
            If Cells(RowIndex, ColumnIndex) = "" Then
                ColumnIndex = ColumnIndex + 1
                If Cells(RowIndex, ColumnIndex) = "" Then
                    DoWhileCondition = False
                Else
                    ColumnIndex = ColumnIndex + 1
                End If
            Else
                ColumnIndex = ColumnIndex + 1
            End If
        Loop
    Thanks again for the help, Phinds.

    If anyone has any idea how I can make it import multiple files in a specific folder I'm all ears. I'm getting the hang of the syntax now and I will try stuff out when I get a chance.
     
    Last edited: Jul 27, 2013
  5. Jul 27, 2013 #4

    phinds

    User Avatar
    Gold Member
    2016 Award

    Uh ... what is "IE"? Are you meaning "for example" ?

    Your code (my modified code) now finds the first non-blank cell in row 1.
     
  6. Jul 27, 2013 #5

    phinds

    User Avatar
    Gold Member
    2016 Award

    Better you should have posted your 2nd edit in a new reply. You've confused things, but if you now know what you're doing, all's well.
     
  7. Jul 27, 2013 #6

    Mark44

    Staff: Mentor

    The abbreviation for "for example" is e.g. (Latin exempli gratia). The abbreviation i.e. is short for id est, also Latin, and means "that is."
     
  8. Jul 28, 2013 #7
    Yes IE was supposed to be e.g. (as Mark44 said), for example. Sorry I was in a hurry and typing fast.

    I also apologize for the edit instead of new posts, I just figured I would make it more succinct but instead made it more confusing. Ill make new posts in the future.

    The code seems to work like I intended. I will post an 'output' picture when I get back to this in a day or so.

    On another note, and I should have concentrated on this first, is it possible to use a variable in the part of the code which tells the program where to find the .dat file? I'm writing from my phone right now so I'll try to explain. The part that says:
    Code (Text):
    connection:= _ TEXT; "c:\PATH\Data.dat"
    Can I change the Data part to a variable ? This way I can try and do a Do-Until/Do-While loop and import multiple .dat files in one run of the macro. For example I would have 1.dat and 2.dat in the source folder and my code would have a variable that would be initialized to 1 and incremented by 1 each run through the loop. This way I envision that I could import many files at once. At the moment I am copying one file at a time into the folder, renaming it Data.dat, running the macro in Excel, deleting the Data.dat and copying another file, renaming, so on and so forth. If I could copy multiple files rename them 1.dat, 2.dat,...,n.dat and run the macro one time it would be a huge time saver.
     
  9. Jul 28, 2013 #8

    phinds

    User Avatar
    Gold Member
    2016 Award

    Of course. Just use a variable name instead of the literal string. That is, use filename instead of "c:\PATH\Data.dat"

    or, for example, use basepath & filename instead of "c:\PATH\Data.dat", where

    basepath = "c:\PATH\" and filename = "Data" & cstr(n) & ".dat" in a loop with n going from 1 to 10, for example so your files are

    c:\PATH\Data1.dat
    c:\PATH\Data2.dat
    .
    .
    .
    c:\PATH\Data10.dat
     
  10. Jul 28, 2013 #9
    Phinds, thank you very much for all of your help. I have successfully automated a large part of some of the most repetitive and boring tasks at work. I've also learned a bit of VB in the process which is a huge plus for me. I finally got it to be able to import multiple files in any given folder. Because I will not know how many trials there will be per workbook I needed a way to make a flexible condition. I did this by assigning the value of cell (1,1) to a variable with "Cells(1,1).Value" and now all I do is put in the number of trials into cell A1, put all the trials into my source folder, rename them 1.dat, 2.dat,...,n.dat and run the macro. Here is the code that works:


    Code (Text):
    Dim RowIndex As Long, ColumnIndex As Long, fileNumb As Long, trials As Double
        Dim DoWhileCondition As Boolean
        Dim basePath As String, fileName As String
       
        fileNumb = 1
        basePath = "TEXT;C:\PATH\"
        fileName = CStr(fileNumb) & ".dat"

        RowIndex = 1
        ColumnIndex = 1
        DoWhileCondition = True
        trials = Cells(1, 1).Value
       
        MsgBox CStr(trials)
       
        For fileNumb = 1 To trials
       
            Do While DoWhileCondition
            If Cells(RowIndex, ColumnIndex) = "" Then
                ColumnIndex = ColumnIndex + 1
                If Cells(RowIndex, ColumnIndex) = "" Then
                    DoWhileCondition = False
                Else
                    ColumnIndex = ColumnIndex + 1
                End If
            Else
                ColumnIndex = ColumnIndex + 1
            End If
            Loop
           
            fileName = CStr(fileNumb) & ".dat"

            With ActiveSheet.QueryTables.Add(Connection:= _
                basePath & fileName, Destination:=Cells(RowIndex, ColumnIndex))
                .Name = D
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 437
                .TextFileStartRow = 20
                .TextFileParseType = xlFixedWidth
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 1)
                .TextFileFixedColumnWidths = Array(6)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
            Columns(ColumnIndex + 1).ColumnWidth = 8
           
        Next fileNumb
    The only thing that I can't figure out now is why it has stopped skipping a column between each trial. This is the latest code that I have and is not really the finished product but I'm going cross eyed from staring at code all evening and I just can't figure out why the macro isn't skipping a column anymore.

    I'm attaching a picture of the output of the macro where the .dat files were named 1 to 10.dat. As you can see it seems to work like a charm except that it won't skip a column anymore. Any pointers are much appreciated but I will be taking a break for the evening. Thanks again for the help!
     

    Attached Files:

  11. Jul 28, 2013 #10

    phinds

    User Avatar
    Gold Member
    2016 Award

    RowIndex, ColumnIndex, and fileNumb should probably be integer. Long is WAY overkill.

    Trials should definitely NOT be a floating point, but an integer.

    It seems very weird to me that you never change RowIndex. Is everything you are doing in row 1?

    I'm not sure what you intend by "skip a column" so can't answer that one.

    There is a way to have the program just read whatever files are there, so that you don't need to tell it in advance how many there are. That is, you can avoid the use of Trials. This is probably overkill for your current state of programming (using Trials the way you have is perfectly reasonable) but if you want to learn how to do it, study the function "dir" --- it's a bit confusing at first, so pay close attention.
     
  12. Jul 29, 2013 #11

    Integral

    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    I assume that you are working in the VB editor. If you position the cursor at the start of a macro and type f8 you can step through the macro and see what is going on. Also f9 will set a breakpoint so you can stop the code at a selected line, then use f8 to step through the code. I find these invaluable trouble shooting aids.
     
  13. Jul 29, 2013 #12
    Duly noted, I was very sloppy with assigning data types. I also found a list of data types in VB online and it didn't have an integer listed but it seems to work okay if I declare "... As Integer".

    I only need to macro to look at row 1 column X (where X is variable) because the data is imported downwards. In other words if row 1 column A is empty I know that the entirety of column A is empty, no need to check row 2 column A etc.

    Please see attachment of what I'm hoping to achieve with the macro. The attachment is what I have done by hand. I hope you can get what I mean by "skipping a column."

    This was an amazing suggestion! The possibilities are now endless! I have written a file counting macro/function, what do you think?

    Code (Text):

    Sub countFiles()

    Dim path As String, file As String, numbFiles As Integer, dummy As String
    Dim fileCount As Integer

    fileCount = 0
    path = "C:\PATH\"
    file = "*.dat"

    dummy = (Dir(path & file))
    MsgBox (dummy)

    If dummy <> "" Then fileCount = fileCount + 1

    Do While dummy <> ""
        dummy = Dir()
        MsgBox (dummy)
        If dummy <> "" Then fileCount = fileCount + 1
    Loop

    MsgBox (CStr(fileCount))

    End Sub
    I have 10 files in my folder named 1.dat, 2.dat, ... , 10.dat. The output is as such (all message boxes of course):

    1.dat
    10.dat
    2.dat
    3.dat
    4.dat
    5.dat
    6.dat
    7.dat
    8.dat
    9.dat
    "" (That is empty string, of however its called)
    10

    The MsgBox's and such are just to make sure all is in order, they will be removed. I think I will be able to adapt this code to make my life EVEN easier! I'm envisioning not having to rename things anymore or anything. Thank you very much phinds, you have been a great help to me.

    The code is probably clunky and not the least bit elegant but it gets the job done (this was my C++ Professor's complaint with all my programming but I think it helped in the end because he KNEW I didn't just use code I found somewhere online and actually wrote it myself).

    This has been an invaluable tip, thanks Integral.

    You guys are really awesome, I know this is probably child's play to guys like you but I'm really proud of what I've been able to do so far! In another life I would definitely be a programmer.
     

    Attached Files:

    Last edited: Jul 29, 2013
  14. Jul 31, 2013 #13
    Argh! I'm almost home free but I'm getting an error if I run the code. Anyone's help is greatly appreciated, I don't know what I did to make it stop working. Please advise.

    Code (Text):

    Sub countFiles()

    Dim path As String, file As String, numbFiles As Integer
    Dim fileCount As Integer
    Dim RowIndex As Integer, ColumnIndex As Integer, fileNumb As Integer
    Dim DoWhileCondition As Boolean
    Dim basePath As String, fileName As String, dummy As String

    fileCount = 0
    path = "TEXT;C:\PATH\"
    file = "*.dat"
    basePath = "TEXT;C:\PATH\"
    fileName = "1.dat"

    dummy = Dir(path & file)
    MsgBox ("1st time we called dir: " & dummy)

    If dummy <> "" Then fileCount = fileCount + 1

    Do While dummy <> ""
        dummy = Dir()
        If dummy <> "" Then fileCount = fileCount + 1
        MsgBox ("This is the counting do while loop: " & dummy)
    Loop

    'MsgBox ("File Count = " & (CStr(fileCount)))

    dummy = Dir(path & file)

    Do While dummy <> ""
        If dummy <> "" Then
            'For fileNumb = 1 To fileCount
     
            Do While DoWhileCondition
            If Cells(RowIndex, ColumnIndex) = "" Then
                ColumnIndex = ColumnIndex + 1
                If Cells(RowIndex, ColumnIndex) = "" Then
                    DoWhileCondition = False
                Else
                    ColumnIndex = ColumnIndex + 1
                End If
            Else
                ColumnIndex = ColumnIndex + 1
            End If
            Loop
           
            MsgBox ("About to hit with statement dummy is now: " & dummy)
           
            With ActiveSheet.QueryTables.Add(Connection:= _
                basePath & dummy, Destination:=Cells(RowIndex, ColumnIndex))
                .Name = D
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 437
                .TextFileStartRow = 20
                .TextFileParseType = xlFixedWidth
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 1)
                .TextFileFixedColumnWidths = Array(6)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
            Columns(ColumnIndex + 1).ColumnWidth = 8
        End If
        'Next fileNumb
        dummy = Dir()
    Loop

    End Sub
     
    The error I get highlights this portion of the code:

    Code (Text):

            With ActiveSheet.QueryTables.Add(Connection:= _
                basePath & dummy, Destination:=Cells(RowIndex, ColumnIndex))
     
    The error I get is: "Application defined or object defined error."

    I'm sort of losing my mind trying to figure this out, I had the same problem the first time I tried to use basePath & Cstr(fileNumb) & ".dat" and I forgot what I did to fix it. It was something dumb like putting in a space/underscore/semicolon etc.

    Everything else seems to work properly, I've "debugged" by using F8 and MsgBox's and my dir() is returning the appropriate file names, the counting is working etc. I thought that dir may not be returning a string so I tried doing "Cstr(dir(argument))" and it didn't work either.

    Its simply the "with" statement which is not working for some strange reason. Any help is much appreciated. Unless I have made a mistake somewhere else and don't even know it.

    P.S. I know the code looks messy and I don't need to do the counting algorithm the way I have it set up, I'm going to clean everything up (get rid of unneeded variables and codes etc) once I have it working properly.
     
  15. Jul 31, 2013 #14

    phinds

    User Avatar
    Gold Member
    2016 Award

    I don't understand

    path = "TEXT;C:\PATH\"

    why is the TEXT; in there and how can that work? Maybe some construct that I'm not familiar with, but it looks wrong to me.
     
  16. Jul 31, 2013 #15
    This part of the code was written by the recording of mouse clicks and what not so I have no idea if it is right or not. That is why it is so frustrating because this is the part of the code that I don't know much about and searching has led me to this link which seems to show the same "TEXT;<path>" syntax.

    Like I said I had this problem when I tried to switch from using a literal string to basePath & Cstr(someVariable) & ".dat" and I fixed it by doing something like adding a space or underscore or some such silly little detail. I'm not having any luck this time around.
     
  17. Jul 31, 2013 #16

    phinds

    User Avatar
    Gold Member
    2016 Award

    OK, looks like there is some parsing going on under the covers that I was not aware of, not ever having used the QueryTables stuff.

    'Fraid I can't help with this one.
     
  18. Aug 1, 2013 #17
    Ah, okay Phinds. Thanks a ton anyway. I'll keep plugging away.
     
  19. Aug 2, 2013 #18
    For any interested parties out there, I have figured out the problem with the latest code I have up here.

    I did not initialize rowIndex or columnIndex to anything. They were 0 and 0 if you were working in a completely blank workbook. Problem solved.

    Thanks for the help Phinds. Greatly appreciated.
     
  20. Aug 2, 2013 #19

    phinds

    User Avatar
    Gold Member
    2016 Award

    Glad to hear you got it working.

    Uninitialized variables in VB and VBA always start off present to zero or null or a blank string or whatever is the appropriate "zero" value.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Excel macro's and VBA
  1. Excel Macros (Replies: 3)

  2. VBA macro programming (Replies: 2)

Loading...