used to worked OK with Win98+XL2K but problems with WinXP+XL2003 now!

J

J_J

Hi,

The below code intends to read data from 2 picked excel *.xlt files, pastes
them on the target file one after the other.
But the problem is that, although it worked without any errors on Win98
Office2K, I am getting complaint messages now. Especially with the repeted

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros Which:=xlAutoOpen

lines...
If I eliminate them it doesn't work at all.
How can I close the read workbooks (after all data is read) with the
filenames beeing as varibles (strFileName1, strFileName2) and locations that
may vary?

'-------------------------
Sub Macro1()
'
' Macro1 Macro
'
'Dim strFileName1 As String
'Dim strFileName2 As String

strFileName1 = Application.GetOpenFilename("Excel Templates (*.xlt),
*.xlt")
If strFileName1 = "" Then Exit Sub
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen

Application.Visible = False

Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B1:B10").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet7").Activate
Range("G5:G19").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B14:B28").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet5").Activate
Range("G5:G12").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B31:B38").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet6").Activate
Range("G5:G11").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B41:B47").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet8").Activate
Range("G5:G13").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B50:B58").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet2").Activate
Range("G5:G9").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B61:B65").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet11").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B68:B71").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet3").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B74:B77").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet4").Activate
Range("G5:G7").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B80:B82").Select
ActiveSheet.Paste , True

'Application.Visible = True

strFileName2 = Application.GetOpenFilename("Excel Templates (*.xlt),
*.xlt")
If strFileName2 = "" Then Exit Sub
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen

Application.Visible = False

Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B1:B10").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet7").Activate
Range("G5:G19").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B14:B28").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet5").Activate
Range("G5:G12").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B31:B38").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet6").Activate
Range("G5:G11").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B41:B47").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet8").Activate
Range("G5:G13").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B50:B58").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet2").Activate
Range("G5:G9").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B61:B65").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet11").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B68:B71").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet3").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B74:B77").Select
ActiveSheet.Paste , True

Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet4").Activate
Range("G5:G7").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B80:B82").Select
ActiveSheet.Paste , True

' Finished copying

Application.Visible = True

Application.CutCopyMode = False

Windows("AnalizR4.xls").Activate
Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A12") = strFileName1
Worksheets("Sheet3").Range("B12") = strFileName2
End Sub
'-----------------------
'--------------------

TIA
J_J
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

apologies for posting it again but... 5
Help neeeded with simplifying this excel code. 3
Help with activating window from Application.GetOpenFilename 4
Using a string variable to change a file name 3
Macro to copy from one source sheet to numerous destination sheets 1
Runtime 1004 Error Help Request 1
Help making this macro dynamic :) 1
I need help with a "Run-Time Error 13: Type Mismatch" 2

Top