Create Workbook and move data

  • err, I had caught that... here is my code now... the one that doesn't hang but doesn't work either...

  • This one works too... but we have to type the filename in... and only one at a time

  • And this was the original... the one that would run more than one file at a time.

  • Try this version.


  • It doesn't work. :? The body of the program

    doesn't like filenames as a Variant... or something.

    There is an error targeting

    Set wbkDownload = Workbooks.Open(Path & Name)

    It couldn't find the workbook... and the path it refers to is funky. It says

    Run-time error '1004':

    'C:\Documents and Settings\Owner\Desktop\Test Run\C:\Documents and Settings\Owner\Desktop\Test Run\EMPIRE07142004.csv' could not be found. Check the spelling of the file name, and verify that the file location is correct.

    If you are trying to open the file from your list of most recently used files on the File menu. make sure that the file has not been renamed.

    End Debug Help

  • Something weird is happening. First, I assumed the new part of the code needed to be the first sub-routine in the code.

    It works (no compile errors) but it has crashed Excel a couple of times (but not every time). When this crash happens, the 19 new files have been created but the program decides to create a 20th file. It doesn't have a downloaded file to use to make a name so it calls it Book 20 and then sometimes Book 21... but crashes soon.

    Several times, when it doesn't crash, it is has created the 19 new files but only put 3 or 1 of the 6 downloaded files data in them. hmmmmm... this is getting very interesting. smile, smile.

    I'll keep looking at it. Lets keep working on it. Your code is amazing. I couldn't do this without you.

  • The order of routines within a module in not important.

    The one that you need to run though is GetImportFileName2

    I have run it a couple of timws now of the 6 data files you provided without problem. That include removing a few of the created workbooks between attempts.

    When you say that Excel crashes do you mean it really crashes or that it produces an error, perhaps offering you the choice to go to debug mode. If so what is the error.


  • Excell crashes. All files are closed and no changes to anything is saved. The subroutine GetImportFileName2 is at the end of the code and the first sub DailyLoad is still at the begining... with the six downloaded files spelled out right in the code. Is that the way it should be? Is that what you have that is working?

    hmmm... I wonder if I changed something that I shouldn't have. :o\

  • Well, this last one works. I was careful to put only the things you have (those six sample data files) through it. Ahhh. thank you. I understand more now about how the subs don't need to be in order as long as I run that macro.


  • Can you see what I'm trying to do. I simply want to delete a column. After emulating what a macro record session says, I realized that "that" code was only deleting the first cell in the column. I then tried copying columns H:M to G:L (shifting everything over). I've gone round and round and it just gets more complicated... which is the wrong direction to go. What do I do?

  • yes, column G and column J. My code looks pretty rediculous but the simple stuff wouldn't work. They also asked me to move a couple of columns around... and, as you can see, change the width of a few (that was the only easy one)

  • Yep. I thought so. I've just tried inserting simple code to move a column which works when I do it by hand... and works when I run a recorded macro... but only moves the first cell of the column when I insert the same code into the body of our previous code. :?

  • It took me a couple minutes... but I think I understand. The original code references the current row (hence, only row one) but the separate subroutine is not locked into that rule. It references the whole sheet (hence, the entire column of rows). whew! Thanks. I will start using more subroutines... I'm coming from old school programing (FORTRAN, C, C++)

    You're tons of help. Thanks. :)

  • Hello Masters of VBA,

    I've found a new snag. I loaded the great program we came up with onto my brother's new laptop to test it on another computer. He bought a used thinkpad, P3 and I loaded Windows XP and Office 97 onto it. So, I copy the .xls file with the macro onto his desktop and run it. To my suprise, there is a compile error. It says " sub or function not defined " and targets InStrRev in the VBA code. Here is the part of the code where it resides.

    Application.ScreenUpdating = False
         '   Display full path and name of the files
        For i = LBound(FileNames) To UBound(FileNames)
            strPath = Left(FileNames(i), InStrRev(FileNames(i), "\"))
            strFile = Mid(FileNames(i), Len(strPath) + 1)
            MyLoad strPath, strFile
        Next i

    Any ideas why?

  • I seemed to have solved my own problem. I updated the Office 98 with an Office 2000 upgrade disk. Now the program works exactly the same as when it's on my computer.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!