Importing content

[Paleo]

Hi guys,

which one do you rather when the data resides in a worksheet from another workbook?

To import a worksheets content or to open the other workbook, copy its content and past it where you want?

Which one is the best and faster?

I have been doing this very often and always believed that to import is best, but now I am having serious problems on doing this.

I am trying to import a worksheet with subtotals and filters and I dont know why I am loosing many rows in the process.

Then I tried to copy the worksheets content and paste it where I want it. Bang again, frozen machine.

The worksheet I am trying to import is HUGE (48,5 Mb).

Any suggestions?

[johnske]

Hi Carlos,

I much prefer Copy & Paste as it's simple and quick but - 48.5 MB on ONE worksheet??? How many rows of data do you have? (Sounds like it needs a cleanup)

John

[Anne Troy]

I agree. I think that file is bad.
I would try copying the worksheet to another workbook before I do anything else.

[Tinku]

I am not sure but if I have to copy from 1 workbook to other or to a new workbook I do it this way

1) Open the workbook with the tab you want to copy..
2) Right click on the tab and press "move or copy"
3) Move or copy to a new workbook or to an existing workbook
or to the same workbook.. guess you are done.

my 2 cents..

Regards
Tinku

[johnske]

Carlos,

First try Jakes cleanup procedure here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=83 and if the file's still too large to copy to a new book, do it in in 'bits' - like - 10 or 20 columns at a time.

Also, if you've set any pictures as background for your worksheets - get rid of them.

Then, if you have a lot of formulas in cells that can be removed and just replaced with their values - do so.

John

[Paleo]

Hi Carlos,

I much prefer Copy & Paste as it's simple and quick but - 48.5 MB on ONE worksheet??? How many rows of data do you have? (Sounds like it needs a cleanup)

John

Hi John,

there are 27,961 rows and 162 columns that are obtained from SAP R/3 and all out of them must be analised by excel, so I cant clean them up.

Carlos,

First try Jakes cleanup procedure here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=83 and if the file's still too large to copy to a new book, do it in in 'bits' - like - 10 or 20 columns at a time.

Also, if you've set any pictures as background for your worksheets - get rid of them.

Then, if you have a lot of formulas in cells that can be removed and just replaced with their values - do so.

John

I already did that clean up, and no it has no pictures as background, only numbers, but lots of them.

[mdmackillop]

Hi Carlos,
I tried it with a simple file, 28000 rows x 180 columns (88MB) generated with Rand()*10000, followed by copy and paste special. (The last bit took a while) It imported easily enough though to another workbook in 15 - 20 seconds. I've got 768MB of RAM.

I know this probably doesn't help, but maybe shows its not an Excel problem.
Regards
Malcolm

[Anne Troy]

Carlos, 3 things.
1--have you tried opening the file with Open Office? That sometimes gets rid of corruption
2--your temp file is empty, yes?
3--if you like, upload to a server and I'll have a crack at it.

[Paleo]

Hi Malcolm,

well I have 512 Mb RAM and it still hanging, .

Hi Anne,

well I dont have Open Office here to test it but I think the error must be at my code, so I am posting it here.

Sub Standard()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    For Each Worksheet In Worksheets
        If Worksheet.Name = "Standard" Then
            strAlert = MsgBox("Voc? est? tentando importar os dados " _
                & "para uma planilha que j? os cont?m. Isto far? com que os dados sejam " _
                & "sobrescritos." & vbCrLf & vbCrLf & "Deseja mesmo fazer isto?", vbQuestion _
                + vbYesNo + vbDefaultButton2, "Importa??o Arriscada")
            If strAlert = vbYes Then
                strAlert2 = MsgBox("Tem certeza?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirma??o")
                If strAlert2 = vbNo Then
                    Exit Sub
                Else
                    Sheets("Standard").Delete
                End If
            Else
                Exit Sub
            End If
        End If
    Next
    Sheets.Add
   Arq = Application.GetOpenFilename("Arquivo do Excel,*.xls")
   PartArq = Split(Arq, "\", -1, vbTextCompare)
   ItensArq = UBound(PartArq)
   NomArq = PartArq(ItensArq)
   Dim Janela As String, Janela2 As String
   Janela = ActiveWindow.Caption
   Workbooks.Open Filename:=Arq
   Janela2 = ActiveWindow.Caption
    Selection.AutoFilter
    Range("D2").Select
    Selection.RemoveSubtotal
    Cells.Select
    Selection.Copy
    Windows(Janela).Activate
    ActiveSheet.Paste
    Range("A1").Select
    Windows(Janela2).Activate
   ActiveWindow.Close savechanges:=False
    Range("A1").Select
    ActiveSheet.Select
    Nome = ActiveSheet.Name
    Sheets(Nome).Name = "Standard"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Oculta_Espera
End Sub

Hope someone can help me out here, .

[johnske]

Hi Carlos,

What is "Nome"? Where did it come from? I can't see it before this:

Nome = ActiveSheet.Name 
    Sheets(Nome).Name = "Standard"

John

[Paleo]

Hi John,

yes, exactly its a non-declared variable . But not the error yet.

[Ken Puls]

Hmmm... Might want to throw an Option Explicit at the top and declare the variables... (Should speed it up a bit, as declared varaibles are faster than variants, too.)

[Paleo]

Doing it now, thanks, but what can be hanging this code??

[Ken Puls]

Don't know. That may even answer the question for you though. Maybe something doesn't like the variant type that's been applied, or you misspelled something? Option Explicit should at least eliminate those potential issues, anyway.

Hey, have you tried stepping through it? At what point does it hang? I assume that there's no errors, it just stops...?

[Paleo]

Hi Ken,

exactly it just stops and it happens after it has copied the whole 27,960 rows.

[Ken Puls]

Hmmm... weird.

Just a thought... I used to use ActiveWindow to move between files, but gave that up in favour of assigning each workbook to a variable. I could then copy blocks of cells (without selecting them) by declaring something along the lines of:

wb1.sheets(1).cells.copy wb2.sheets(2).range("A1")

I dont' know if that would make a difference or not, but it's an easier practice, IMHO.

[Paleo]

Good, I will test it.

Another thing. I am creating a word document dinamically from this workbook and now that I putted the Option Explicit its complaining about a "wdToggle" propertie from word.

How should I declare it??

[Ken Puls]

Hi,

Using a late bind? Sub out the wdToggle for the numerical equivalent. I checked the object browser in Word, and I found: Const wdToggle = 9999998 (&H98967E)

I think you only need the 9999998

Let me know if that's not it.

[johnske]

Instead of:

Cells.Select 
Selection.Copy 
Windows(Janela).Activate 
ActiveSheet.Paste 
Range("A1").Select

Try something like:

Cells.Copy Destination:=Sheets(Janela).[a1]
'Then - if you need to...
Sheets(Janela).Activate
[a1].Select

[Paleo]

Hi Ken,

you good, yes a little faster but faster. Can you tell me what is:
wdColorDarkBlue
wdAlignParagraphCenter
wdSortByName
wdPasteMetafilePicture
wdFloatOvertext
wdFormatDocument