Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
I have data in "Mailer List.xls" On the sheet labeled "mor01001" of this workbook I need to bring in th data cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 I need a macro that I can run once a month to import this data int "Direct Mailer Template.xls" It would need to be imported to the sheet labeled "Helper Sheet" in th corresponding cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G400 -- jermsalerm ----------------------------------------------------------------------- jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016 View this thread: http://www.excelforum.com/showthread.php?threadid=49854 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
Sub c()
srcpath = "C:\SFADB" srcbook = "random.xls" srcsheet = "R1" aydes = Array("c1:c6", "d1:d6", "e1:e6") aysource = Array("$A$5:$A$10", "$b$5:$b$10", "$b$5:$b$10") Set wsdes = Sheet For i = LBound(aydes) To UBound(aydes) With wsdes.Range(aydes(i)) .FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'!" & aysource(i) .Copy .PasteSpecial xlPasteValues End With Next i Application.CutCopyMode = False Set wsdes = Nothing End Sub edit to fit - voodooJoe "jermsalerms" wrote in message ... I have data in "Mailer List.xls" On the sheet labeled "mor01001" of this workbook I need to bring in the data cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 I need a macro that I can run once a month to import this data into "Direct Mailer Template.xls" It would need to be imported to the sheet labeled "Helper Sheet" in the corresponding cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=498548 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
The following first code below will work when your source is a single
contiguous range. However, since your Source has several areas you will need to add a loop to copy one area at a time. See bottom: Option Explicit Sub CopyFromTest1() Dim sFile As String Dim Wbk As Workbook Dim Source As Range Dim Dest As Range On Error Resume Next Set Wbk = Workbooks("Test1") If Wbk Is Nothing Then 'its not open sFile = ThisWorkbook.Path & "\Test1.xls" Set Wbk = Workbooks.Open(sFile) End If If Wbk Is Nothing Then MsgBox "Unable to find the file: " & sFile, vbOKOnly, "ERROR" Exit Sub End If Set Source = Wbk.Sheets(1).Range("A1:A2") Set Dest = ThisWorkbook.Sheets(1).Range("A1:A2") Source.Copy Dest Wbk.Close False End Sub Redefine the Source and Dest to include all of the areas, keeping them in the same order. Add this variable: dim i as integer Then insert or change the copy part to: For i=1 to Source.Areas.Count Source.Area(i).Copy Dest.Area(i) i=i+1 Next i "jermsalerms" wrote: I have data in "Mailer List.xls" On the sheet labeled "mor01001" of this workbook I need to bring in the data cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 I need a macro that I can run once a month to import this data into "Direct Mailer Template.xls" It would need to be imported to the sheet labeled "Helper Sheet" in the corresponding cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=498548 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
Hi jermsalerms,
The ranges you've given for the copy and paste (A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001, F2:F4001, G2:G4001) corresponds with the single range A2:G4001. In which case a single copy-paste is all that's required... Sub ImportData() Application.ScreenUpdating = False On Error Resume Next '< error = Mailer List.xls is already open Workbooks.Open ("C:\Windows\Desktop\Mailer List.xls") Sheets("mor01001").Range("A2:G4001").Copy _ Destination:=ThisWorkbook.Sheets("Helper Sheet").Range("A2") Workbooks("Mailer List.xls").Close False Application.ScreenUpdating = True End Sub HTH, John -- The major part of getting the right answer lies in asking the right question... "jermsalerms" wrote: I have data in "Mailer List.xls" On the sheet labeled "mor01001" of this workbook I need to bring in the data cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 I need a macro that I can run once a month to import this data into "Direct Mailer Template.xls" It would need to be imported to the sheet labeled "Helper Sheet" in the corresponding cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=498548 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
I wasn't as observant as John to see that it really is only one range, that
makes it a lot easier. but there really isn't a need to open the source workbook to get the values - use a link formula then copy/pastespecial values Sub X() srcpath = "C:\SFADB" srcbook = "random.xls" srcsheet = "R1" srcrng = "A2:G4001" With ActiveSheet .Range(srcrange).FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'!" & srcrng .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub - vdJ "John Skewes" wrote in message ... Hi jermsalerms, The ranges you've given for the copy and paste (A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001, F2:F4001, G2:G4001) corresponds with the single range A2:G4001. In which case a single copy-paste is all that's required... Sub ImportData() Application.ScreenUpdating = False On Error Resume Next '< error = Mailer List.xls is already open Workbooks.Open ("C:\Windows\Desktop\Mailer List.xls") Sheets("mor01001").Range("A2:G4001").Copy _ Destination:=ThisWorkbook.Sheets("Helper Sheet").Range("A2") Workbooks("Mailer List.xls").Close False Application.ScreenUpdating = True End Sub HTH, John -- The major part of getting the right answer lies in asking the right question... "jermsalerms" wrote: I have data in "Mailer List.xls" On the sheet labeled "mor01001" of this workbook I need to bring in the data cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 I need a macro that I can run once a month to import this data into "Direct Mailer Template.xls" It would need to be imported to the sheet labeled "Helper Sheet" in the corresponding cells A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001 -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=498548 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
I edited the formula to fit but it is giving me a "Compile Error: Syntax Error" Here is the formula...am I missing something? Sub X() srcpath = "C:\Documents and Settings\user\My Documents\Spreadsheets\Data" srcbook = "Mailer List.xls" srcsheet = "mor01001" srcrng = "A2:G4001" With ActiveSheet ..Range(srcrange).FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'!" & srcrng ..Copy ..PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=498548 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
minor syntax error(s). edit to suit.
Sub X() srcpath = "C:\SFADB" srcbook = "ran dom.xls" srcsheet = "R1" srcrng = "d4:e5" With Sheet1.Range(srcrng) 'correct variable and add to with statement so it applies to copy and pastespec methods as well .FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'!" & srcrng .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub cheers - voodooJoe "jermsalerms" wrote in message ... I edited the formula to fit but it is giving me a "Compile Error: Syntax Error" Here is the formula...am I missing something? Sub X() srcpath = "C:\Documents and Settings\user\My Documents\Spreadsheets\Data" srcbook = "Mailer List.xls" srcsheet = "mor01001" srcrng = "A2:G4001" With ActiveSheet .Range(srcrange).FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'!" & srcrng .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=498548 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to import from one closed workbook
This is what I have entered into Visual Basic...when I run the macro get error 400 Sub X() srcpath = "C:\Documents and Settings\user\M Documents\Spreadsheets\Data" srcbook = "InfoUSA List.xls" srcsheet = "mor01001" srcrng = "a2:g4001" With Sheet1.Range(srcrng) 'correct variable and add to with statemen so it applies to copy and pastespec methods as well .FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'! & srcrng .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Su -- jermsalerm ----------------------------------------------------------------------- jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016 View this thread: http://www.excelforum.com/showthread.php?threadid=49854 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
closed workbook macro | Excel Discussion (Misc queries) | |||
Import data from a closed workbook | Excel Discussion (Misc queries) | |||
import all sheets from a closed workbook | Excel Programming | |||
Import Sheet from Closed Workbook | Excel Programming | |||
Using Querytable.add to import data from a closed workbook | Excel Programming |