PDA

View Full Version : [SOLVED] Importing content



Paleo
03-21-2005, 09:04 AM
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
03-21-2005, 09:27 AM
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
03-21-2005, 10:48 AM
I agree. I think that file is bad.
I would try copying the worksheet to another workbook before I do anything else.

Tinku
03-21-2005, 10:57 AM
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..:think: guess you are done.

my 2 cents..

Regards
Tinku

johnske
03-21-2005, 11:12 AM
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
03-21-2005, 12:42 PM
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.:mkay

mdmackillop
03-21-2005, 01:53 PM
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
03-21-2005, 01:58 PM
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
03-22-2005, 04:22 PM
Hi Malcolm,

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

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, :banghead: : pray2: : pray2: .

johnske
03-22-2005, 04:53 PM
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
03-22-2005, 05:00 PM
Hi John,

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

Ken Puls
03-22-2005, 05:01 PM
Hmmm... Might want to throw an Option Explicit at the top and declare the variables... :yes (Should speed it up a bit, as declared varaibles are faster than variants, too.)

Paleo
03-22-2005, 05:06 PM
Doing it now, thanks, but what can be hanging this code??:dunno

Ken Puls
03-22-2005, 05:12 PM
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
03-22-2005, 05:15 PM
Hi Ken,

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

Ken Puls
03-22-2005, 05:19 PM
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
03-22-2005, 05:34 PM
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
03-22-2005, 05:36 PM
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
03-22-2005, 05:46 PM
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
03-22-2005, 05:46 PM
Hi Ken,

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

Ken Puls
03-22-2005, 05:58 PM
Sure,

Const wdColorDarkBlue = 8388608 (&H800000)
Const wdAlignParagraphCenter = 1
Const wdSortByName = 0
Const wdPasteMetafilePicture = 3
Const wdFloatOverText = 1
Const wdFormatDocument = 0

You can actually pick all these up yourself by going to the object browser in Word. Type in the constant, hit the search, and then select the appropriate constant that appears in the window. In the very bottom window, this stuff is there.

HTH,

Paleo
03-22-2005, 06:02 PM
Great Ken,

many thanks. I will test to attribute a variable to each workbook and see if it works now.


New problem with this code:


Sub Mostra_Espera()
Dim VR As Range, MyShape As Shape, mySel As Selection
Set VR = ActiveWindow.VisibleRange
Set MyShape = Sheets("Espera").Shapes("Espera")
Set mySel = Selection
MyShape.Copy
Dim T As Long, L As Long
T = VR.Top + VR.Height / 2 - MyShape.Height / 2
L = VR.Left + VR.Width / 2 - MyShape.Width / 2
ActiveSheet.Paste
With ActiveSheet.Shapes("Espera")
.Top = T
.Left = L
End With
VR.Resize(1, 1).Select
Set MyShape = Nothing
End Sub
Sub Oculta_Espera()
On Error Resume Next
ActiveSheet.Shapes("Espera").Delete
Application.ScreenUpdating = True
mySel.Select <- This row
Set mySel = Nothing
End Sub


Its telling me the variable "mySel" is not declared and if I declare it, then tells "Select" isnt a valid property. Now what should I do?

johnske
03-22-2005, 06:28 PM
Carlos,

With respect to your original 'copy' problem, the "Windows" is a bad idea, also, you should be getting an error here > ActiveSheet.Paste

Try my last post on this (or Kens' post just above that)
John

Paleo
03-22-2005, 06:31 PM
Hi John,

no, I didnt get an error on ActiveSheet.Paste, actually it worked fine, but yes I am trying what you both suggested.

I am just doing some corrections first to use Option Explicit as Ken said it makes the code run faster.

johnske
03-22-2005, 06:35 PM
Well, I get a run-time error 1004 if I use ActiveSheet.Paste

Paleo
03-22-2005, 06:37 PM
Gee thats strange 'cause I didnt. I wonder if Ken got it too. Well, maybe I would get it if my system doesnt hangs :dunno

XL-Dennis
03-22-2005, 07:25 PM
Carlos,

I'm late as usual nowadays...

Anyway, I must ask why You in the first place are working with a file with a size of nearly 50 MB? Have You considered to use Access for it, i e to store the data in and use ADO/SQL to retrieve relevant data for different kind of analysis in Excel?

Each user does not need to have a license of Access to retrieve data from the database.

We can also control how to import data into the database from Excel as well as other data manipulation.

Of course the physical available RAM have some impact but keep in mind that Excel and memory is not a happy marriage per se as Excel have what is called memory leaks.
Make a search at MSFT KB and You will come up with some articles on that subject.

Except for that Excel have a limit on how much it can work with irrespectively if we have 510 MB RAM or 4 GB RAM available.

When working with large amount of data I sometime create databases on the fly, import the data, retrieve relevant data into Excel and sometime delete the database to cleaning up. Nothing fancy but yet very powerful. When creating databases on the fly we don't need a license of Access only make sure that ADO 2.5 is available on the computer(s).

Kind regards,
Dennis

Paleo
03-22-2005, 09:20 PM
Hi Dennis,

well as on the place I am using this there is no Access license available and I though it would be needed I didnt even consider on using Access and other than that I must procede with so many calculations on that data I dont see how to make that fast without having all the data in the workbook.

Ken, I am trying to use your solution but am having some problems on how to declare the variables, can you help me out? I am using this:


Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook.FullName

Ken Puls
03-22-2005, 10:23 PM
Hi Carlos,

You're overthinking it a bit! :)


Sub test()
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
MsgBox wb1.FullName
End Sub

PS...



mySel As Selection

Try...


mySel As Range

It also looks like the variable declaration and Set statement are missing from this part:



Sub Oculta_Espera()
On Error Resume Next
ActiveSheet.Shapes("Espera").Delete
Application.ScreenUpdating = True
mySel.Select <- This row
Set mySel = Nothing
End Sub

Paleo
03-22-2005, 10:50 PM
Hi Ken,

well now I am getting a 438 error:

My code is:



Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Workbooks.Open Filename:=Arq
Set wb2 = ActiveWorkbook
With wb2
.Selection.AutoFilter ' <- This line with the error
.Range("D2").Select
.Selection.RemoveSubtotal
.Cells.Select
.Selection.Copy
End With
With wb1
.ActiveSheet.Paste
' Seleciona a c?lula A1
.Range("A1").Select
End With



Answering your post all the variables from Oculta_Espera where declared at the preceding Sub and are only being called back here.

Ken Puls
03-22-2005, 11:12 PM
I'm not sure what you're trying to accomplish by setting the autofilter, but you need to specify a range with only 1 row (I think).

Not knowing exactly which range you want, I tried this, and it works:


With wb2
.Sheets(1).Rows(1).EntireRow.AutoFilter
.Range("D2").Select
End With

mdmackillop
03-23-2005, 01:42 AM
Hi Carlos,
Going back to this bit, should it not be MySel as Range?


Sub Mostra_Espera()
Dim VR As Range, MyShape As Shape, mySel As Selection
Set VR = ActiveWindow.VisibleRange
Set MyShape = Sheets("Espera").Shapes("Espera")
Set mySel = Selection

sandam
03-23-2005, 04:02 AM
And now for my 2c. I've never had any luck decalring selection objects. i always seem to get an error of some kind. The wd Constants can usually be solved by making sure that the Word object library and Office object library are a part of the workbook's references. I think MD hs a good idea with making it a range object instead. then you just have to assign the range you want and mySel.Select should work fine.

Paleo
03-23-2005, 06:01 AM
Hi guys,

Ken: Actually I want to remove the AutoFilter that exists in that workbook, thats why I am setting it without any cell.

MD and Sandam: Yes guys, you right as Ken had stated before, I am changing it to mySel As Range.

Ken Puls
03-23-2005, 09:43 AM
Ah! Okay. Give this a shot instead:




With wb2
.Sheets(1).AutoFilterMode = False
.Range("D2").Select
End With

Paleo
03-23-2005, 10:47 AM
Hi Ken,

great this one is solved, but now I got error 438 here:



With wb2
With .Sheets("CN")
.AutoFilterMode = False
.Range("D2").Select
.Selection.RemoveSubtotal ' This line has the error
.Cells.Select
.Selection.Copy
End With
End With

Ken Puls
03-23-2005, 10:56 AM
Hey Carlos,

I have to tell you that I absolutely loath the Select method and avoid it wherever possible. (Well, almost always, anyway. There are a very few times when it may be appropriate.)

Try going with the following. Not tested, but I think it should work:



With wb2
With .Sheets("CN")
.AutoFilterMode = False
.Range("D2").Select
.Cells.RemoveSubtotal
.Cells.Copy
End With
End With

Paleo
03-23-2005, 11:04 AM
Hi Ken,

great, this was another step forward. Now the problems (error 438) is here:



Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Workbooks.Open Filename:=Arq
Set wb2 = ActiveWorkbook
With wb2
With .Sheets("CN")
.AutoFilterMode = False
.Range("D2").Select
.Cells.RemoveSubtotal
.Cells.Copy
End With
End With
With wb1
.ActiveSheet.Paste
.Range("A1").Select ' This is the error
End With


If I change that line to


.ActiveSheet.Range("A1").Select


Then I get error 1004.

Ken Puls
03-23-2005, 11:08 AM
You're close, I think. Your code is trying to reference a cell in a workbook, without telling it the sheet, but I'm not sure if activesheet is what you want. Activeanything is actually another one I try to avoid, making explicit references. I find that there's just less chance of error.

Try using .sheets(1) or .worksheets("thesheetsname") instead of .activesheet and see if that makes a difference.

Paleo
03-23-2005, 11:21 AM
Hi Ken,

stills the same:



With wb1
With .Sheets(Nome)
.Paste
.Range("A1").Select ' Error here
End With
End With


Error 1004.

Ken Puls
03-23-2005, 11:33 AM
:doh:Gotta activate the sheet before you select the range.



With wb1
With .Sheets(Nome)
.Paste
.Activate
.Range("A1").Select
End With
End With

Paleo
03-23-2005, 11:55 AM
Gee, what a goofed:bug: :omg2: .

Now that code is working great, but when the code comes to this line:


wb2.Close savechanges:=False


the system simply hangs. I just need to close the wb2. What I am doing wrong now?:banghead:

Zack Barresse
03-23-2005, 11:57 AM
Not sure, but I always just leech off the native syntax ..


wb2.Close False

Does it make a difference?

Ken Puls
03-23-2005, 11:57 AM
Okay, that's strange... I don't see anything wrong with that line...

Paleo
03-23-2005, 12:05 PM
Well this is my whole logic if it helps:



Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Workbooks.Open Filename:=Arq
Set wb2 = ActiveWorkbook
With wb2
With .Sheets("CN")
.AutoFilterMode = False
.Range("D2").Select
.Cells.RemoveSubtotal
.Cells.Copy
End With
End With
With wb1
With .Sheets(Nome)
.Paste
.Activate
' Seleciona a c?lula A1
.Range("A1").Select
End With
End With
wb2.Close savechanges:=False ' System hangs here
Range("A1").Select
ActiveSheet.Select
Sheets(Nome).Name = "Standard"


Testing Zacks advice.

Sorry Zack, hanged the same way.

Zack Barresse
03-23-2005, 12:46 PM
I'm wondering if we can't boil that code down to something like this ...


Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open(Arq)
With wb2.Sheets("CN")
.AutoFilterMode = False
.Range("D2").Select
.Cells.RemoveSubtotal
.Cells.Copy wb1.Sheets(Nome).Range("A1")
End With
wb2.Close False
Range("A1").Select
Sheets(Nome).Name = "Standard"

Ken Puls
03-23-2005, 12:48 PM
And do you even need the line:



.Range("D2").Select

It doesn't seem to serve much purpose to me.

Paleo
03-23-2005, 12:58 PM
Hi guys,

Zack: Great, thanks for the Sub diet. It worked great till the end. No more problems.

Ken: You right, I have removed it.

I am marking it solved now, thank you very much Ken and Zack!:bow: :bow: :bow: :bow:

Zack Barresse
03-23-2005, 01:02 PM
.. It worked great till the end. No more problems. ..

Carlos, does that mean it doesn't hang anymore?

Paleo
03-23-2005, 01:09 PM
Yes, exactly. Worked just fine. No more hanging.

Ken Puls
03-23-2005, 02:26 PM
Ah, excellent!

johnske
03-23-2005, 02:29 PM
Which all goes to prove three points:

1) Using option explicit removes one potential source for errors

2) Declaring your variables properly removes another potential source for errors

3) Removing all unnecessary code and cutting it down to the bare essentials then removes all other potential sources of errors (and makes it MUCH easier to read the underlying logic)

Good work you guys! :thumb

Zack Barresse
03-23-2005, 02:30 PM
Glad it works for you Carlos! :yes

John: Thanks buddy!! :)

Ken: Always a pleasure working with ya! ;)

Ken Puls
03-23-2005, 02:34 PM
John, agreed! Nice recap! :yes

It's been a fun one!

Paleo
03-23-2005, 02:47 PM
Many thanks to you all guys!! I was stuck what that and now its solved!! Thanks!!!

:beerchug: