Create Workbook and move data

Eskkimo · Jul 15th 2004

I need to take every row (2 through...) from a downloaded spreadsheet and place them in their own spreadsheets. The next day a new spreadsheet will be downloaded and it's rows will need to be added to the previous spreadsheets that were created the first time. This process needs to be done every day.

Can we create a macro or write VBA code to do it? I'm stuck on how to do loops when creating new workbooks or worksheets... every time i run the program.

Here is an example of the downloaded spreadsheet... and a second example representing the following day's downloaded spreadsheet.

**Andy Pope** · Jul 15th 2004

Hi Eskkimo,

We need some more information here.

What determines the spreadsheet that each row goes to???
Do this spreadsheets have naming convention.

Eskkimo · Jul 15th 2004

The column named Number defines how many spreadsheets need to be generated. There are 19 rows in the sample, so that would generate 19 spreadsheets. I've attached an example of what one of these spreadsheets (one of 19) would look like if it had been updated six times.

This macro works but only once. Then, I have to delete what I have generated and start over. It is only generating one spreadsheet when it will needs to generate 19 (for this group). Others may have as many as 200.

Another dilema is that my macro only works when all six sample-data sheets are open. In reality, I will only get one of these per day, and I need to add it's data to the 19 spreadsheets.

I hope this example helps.

Eva

P.S. the naming convention could be based on the Number

**Andy Pope** · Jul 15th 2004

The wbk is empty.

Can you also include any existing macro code you have.

Eskkimo · Jul 15th 2004

I was affraid you would ask for that. It's long and tedious... but here goes nothing. it's referencing six different spreadsheets.

I couldn't figure out how to get a loop to work with the names of the downloaded spreadsheets. The real names look like this:
EMPIRE071320040034.csv
EMPIRE071420041343.csv
EMPIRE071520041207.csv

The date of the download is in there.

Code

Sub AddBook()
'
' AddBook Macro
' Macro recorded 7/15/2004 by Eva Norman
'


'
    Rows("2:2").Select
    Selection.Copy
    Workbooks.Add
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Book1.xls", FileFormat:=xlNormal _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    Rows("2:2").Select
    Windows("DownloadedSpreadsheet.xls").Activate
    Selection.Copy
    Windows("Book1.xls").Activate
    ActiveSheet.Paste
    
    Windows("SecondDwnldSpreadsheet.xls").Activate
    Rows("2:2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Windows("Book1.xls").Activate
    Rows("3:3").Select
    Windows("SecondDwnldSpreadsheet.xls").Activate
    Selection.Copy
    Windows("Book1.xls").Activate
    ActiveSheet.Paste


    Windows("ThirdDownloadedSpreadsheet.xls").Activate
    Rows("2:2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Windows("Book1.xls").Activate
    Rows("4:4").Select
    Windows("ThirdDownloadedSpreadsheet.xls").Activate
    Selection.Copy
    Windows("Book1.xls").Activate
    ActiveSheet.Paste
    
    Windows("Fourth.xls").Activate
    Rows("2:2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Windows("Book1.xls").Activate
    Rows("5:5").Select
    Windows("Fourth.xls").Activate
    Selection.Copy
    Windows("Book1.xls").Activate
    ActiveSheet.Paste


    Windows("Fifth.xls").Activate
    Rows("2:2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Windows("Book1.xls").Activate
    Rows("6:6").Select
    Windows("Fifth.xls").Activate
    Selection.Copy
    Windows("Book1.xls").Activate
    ActiveSheet.Paste
    
    Windows("Sixth.xls").Activate
    Rows("2:2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Windows("Book1.xls").Activate
    Rows("7:7").Select
    Windows("Sixth.xls").Activate
    Selection.Copy
    Windows("Book1.xls").Activate
    ActiveSheet.Paste
    
End Sub

Display More

Eskkimo · Jul 15th 2004

Here are the third and fourth sample-data spreadsheets

**Andy Pope** · Jul 16th 2004

The attached contains some code to process your download workbooks.

Post back if anything is not clear

Eskkimo · Jul 16th 2004

Thanks, The first Debugging clue is on line--->>>

Set wbkDownload = Workbooks.Open(Path & Name)

in Sub MyLoad

**Andy Pope** · Jul 16th 2004

Sorry, does that mean its working or not?

Eskkimo · Jul 16th 2004

It's not working. There seems to be an error on the line I referenced. I'm assuming it works on your computer. Why would it be different over here?

**Andy Pope** · Jul 16th 2004

Depends what the error is but I would have thougth the path and name do not contain the correct information.

add the following before the line of code to see the contents
Msgbox Path & Name

Eskkimo · Jul 16th 2004

Ahhhhh, yes. It says

C:\Documents and Settings\Owner\Desktop\DownloadedSpreadsheet.xls

What part of the code do I put that in? Or should I just move the location of the spreadsheet?

**Andy Pope** · Jul 16th 2004

Put the workbook with the code into the same folder as the download and output workbooks if you want to continue ti use Thisworkbook.path
otherwise change Thisworkbook.Path to the path you actual want to use.

Eskkimo · Jul 16th 2004

Oh My God!!! This is amazing!!!!! This has been unbelievablly helpful. I've learned so much - and my boss will be thrilled. THANKS!!!! :gift: :thanx: I owe you one.

**Andy Pope** · Jul 16th 2004

Your welcome!

Eskkimo · Jul 19th 2004

LBound and UBound from Excel's GetOpenFilename method

I must ask for a little more assistance on this project.

I'm adding the user interface parts to the previous code and the perfect example from the book, Excel Bible (pg 1072), won't work. There is something funny about LBound and UBound. It's expecting an array... I'm not sure what to do.

I've erased that section of the code... then it tries to run but gets caught on the line defining the FileName.

Can anyone tell me what to do?

Here is the code:

Code

Option Explicit
Sub GetImportFileName2()
    Dim Filt As String
    Dim FilterIndex As Long
    Dim Title As String
    Dim FileName As String
    Dim i As Long
    
'   Set up list of file filters
    Filt = "Microsoft Excel Worksheet (*.xls),*.xls," & _
        "Comma Separated Files (*.csv),*.csv,"
'   Display *.* by default
    FilterIndex = 5
'Set the dialog box caption
    Title = "Select a File to Import"
'Get the file name
    FileName = Application.GetOpenFilename _
        (FileFilter:=Filt, _
         FilterIndex:=FilterIndex, _
         Title:=Title, _
         MultiSelect:=True)
'   Exit if dialog box canceled
    If Not IsArray(FileName) Then
        MsgBox "No file was selected."
        Exit Sub
    End If
    
'   Display full path and name of the files
    For i = LBound(FileName) To UBound(FileName)
        Msg = Msg & FileName(i) & vbCrLf
    Next i
    MsgBox "You selected:" & vbCrLf & Msg




    
    Application.ScreenUpdating = False
    MyLoad ThisWorkbook.Path & "\", FileName
    Application.ScreenUpdating = True
End Sub

Display More

**Andy Pope** · Jul 19th 2004

Hi,

LBound is complaining because you have declared Filename as a Non array string variable.
You need to change the DIm line to,

Dim FileName As Variant

Eskkimo · Jul 19th 2004

Yes. And when I do that I get a compile error: ByRef argument mismatch... as it refers to the second to last line when I use filename again.

I started another string... and recieved the same advice... but the code for creating new workbooks doesn't seem to work with the dialogue box code.

So this code was added to the previous advice

Code

'   Display full path and name of the files
    For i = LBound(FileName) To UBound(FileName)
        Msg = Msg & FileName(i) & vbCrLf
        Workbooks.Open FileName:=FileName(i)
    Next i


End Sub

and then I tried to combine it with our previous work of art like this

Code

'   Display full path and name of the files
    For i = LBound(FileName) To UBound(FileName)
        Msg = Msg & FileName(i) & vbCrLf
        Workbooks.Open FileName:=FileName(i)
    Next i
    Application.ScreenUpdating = False
    
    ' under normal use the MyLoad would only be called once
    
    MyLoad ThisWorkbook.Path & "\", FileName
    Application.ScreenUpdating = True
End Sub

Display More

it probablly need to be combined with the GetOutput sub not the EnterFilename Sub... but I'm confused now.

**Andy Pope** · Jul 19th 2004

Either change the variable type in the MyLoad routine.
I haven't got the code here but something like

Sub MyLoad(Path as String , File as variant)

Or convert the argument that you pass it, something like
MyLoad ThisWorkbook.Path & "\", Cstr(FileName(i))

Eskkimo · Jul 19th 2004

It opens the six downloaded files but doesn't create the 19 new ones.

And then it hangs on this part of the code

MyLoad ThisWorkbook.Path & "\", CStr(FileName(i))

So I add the other suggestion you give and there are no compile errors but the program does not perform. :o(

I keep trying different arrangements.

Create Workbook and move data

Participate now!

Share