Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
used to worked OK with Win98+XL2K but problems with WinXP+XL2003 now!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel problems after winXP sp3 update | Excel Discussion (Misc queries) | |||
Pasting differences with win98 and winxp | Excel Discussion (Misc queries) | |||
VBA Using 2 OS (Win98 & Winxp) | Excel Programming | |||
VBA Using 2 OS (Win98 & Winxp) | Excel Programming | |||
Problems with xl97 & xl2K | Excel Programming |