Macro to import from one closed workbook

**jermsalerms** · 01-05-2006, 08:21 PM

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

voodooJoe · 01-05-2006, 10:10 PM

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

cush · 01-06-2006, 12:34 AM

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
>
>

John Skewes · 01-06-2006, 12:34 AM

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
>
>

voodooJoe · 01-06-2006, 01:40 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

**jermsalerms** · 01-06-2006, 10:57 AM

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

voodooJoe · 01-06-2006, 04:30 PM

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

**jermsalerms** · 01-06-2006, 06:05 PM

This is what I have entered into Visual Basic...when I run the macro I get error 400

Sub X()
srcpath = "C:\Documents and Settings\user\My Documents\Spreadsheets\Data"
srcbook = "InfoUSA List.xls"
srcsheet = "mor01001"
srcrng = "a2:g4001"

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

**jermsalerms** · 01-10-2006, 12:44 PM

I figured out the error 400 has to do with protected sheets...

now my problem is that everything is supposed to be importing to sheet two and it goes to sheet one.

The code is written on sheet two...is there something I am doing wrong...or is there extra coding that needs to be in there to make it work correctly.

I must have it go to sheet two because sheet one gets imported into a contact manager that reads the first sheet only.