Importing content

[Ken Puls]

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]

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]

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]

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]

Well, I get a run-time error 1004 if I use ActiveSheet.Paste

[Paleo]

Gee thats strange 'cause I didnt. I wonder if Ken got it too. Well, maybe I would get it if my system doesnt hangs

[XL-Dennis]

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]

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]

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]

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]

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]

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]

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]

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]

Ah! Okay. Give this a shot instead:

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

[Paleo]

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]

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]

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]

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]

Hi Ken,

stills the same:

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

Error 1004.