PDA

View Full Version : Vba open workbook and paste from another workbook



rohan8510
02-21-2015, 04:38 AM
hi can you please help me with this code keep getting the error "object variable or with variable not set"

many thanks




Sub test()

Dim fname As String
Dim fpath As String
Dim owb As Workbook

fname = "DummyXXAC.xlsm"
fpath = "C:\Users\USER\Desktop"
owb = Application.Workbooks.Open(fpath & "\" & fname)
Windows("CNAV.xlsm").Activate
Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Ccy", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks(DummyXXAC.xlsm).Sheets("Sheet1").Range("d2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats

End Sub

p45cal
02-21-2015, 05:05 AM
Try changing this line:
owb = Application.Workbooks.Open(fpath & "\" & fname)
to:
Set owb = Application.Workbooks.Open(fpath & "\" & fname)

also the following line may not work:
Set rngPaste = Workbooks(DummyXXAC.xlsm).Sheets("Sheet1").Range("d2")
so make it either:
Set rngPaste = Workbooks("DummyXXAC.xlsm").Sheets("Sheet1").Range("d2")
or:
Set rngPaste = Workbooks(fname).Sheets("Sheet1").Range("d2")
All untested.

rohan8510
02-21-2015, 05:50 AM
You are a Genius thank youuuuu soooooo much. It was really helpful :):)

rohan8510
02-23-2015, 02:55 AM
hi,

could you please help me with this code, error say invalid use of property, many thanks



Windows ("CNAV" & "" & "IACB" & "" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").Activate)

p45cal
02-23-2015, 03:08 AM
Windows("CNAVIACB" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd")).Activate
?

rohan8510
02-23-2015, 03:42 AM
Windows("CNAVIACB" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd")).Activate
?

hi p45 thanks for your reply but you see the name of the file is CNAV IACB yyyymmdd, there is a space between the words CNAV and IACB.

should i still proceed waiting for your advice many thanks :)

rohan8510
02-23-2015, 03:45 AM
added CNAV and IACB and yyyymmdd

hi p45 thanks for your reply but you see the name of the file is CNAV IACB yyyymmdd, there is a space between the words CNAV and IACB and yyyymmdd

should i still proceed waiting for your advice many thanks

p45cal
02-23-2015, 03:52 AM
Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd")).Activate

rohan8510
02-23-2015, 04:15 AM
hi p45 i have tried yur code but it says compile error invalid use of property, can help me on this please thanks :)

p45cal
02-23-2015, 06:11 AM
You're going to have to do some detective work.
When you get the error, in the Immediate pane of the vbe (Ctrl+G ifyou can't see it), type or copy:
?Sheets("Main").Range("C6").Value
and press Enter. Do you see the contents of cell C6 as you expect? (Make sure that when you do this the workbook that would normally be active at this juncture in the code, is the active workbook (the sheet Main shoulkd be in the active workbook).)

If at this point you get the same error as before, put an apostrophe at the extreme left of the highlighted line of code to comment it out temporarily, and try again.

If all OK, then enter the following in the Immediate pane:
?"]" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & "["
and press Enter. Does the result (the date part only of the filename) look right between the ] and [ characters?

If so then enter:
?"]" & "CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & "["
Press Enter. Does the full file name look right? If so add .xls, or .xlsm or whatever the file name extension is, as follows:
?"]" & "CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx" & "["
Press Enter. Does it look right?
If so enter the following:
Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate
and press Enter. Does it activate that workbook/window?

By now you may well have solved the problem..

rohan8510
02-23-2015, 07:43 AM
hi, thanks for your reply this part gave me the correct date ?Sheets("Main").Range("C6").Value

but ?"]" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & "[" gave me run time error 6 overflow

could you please help me with this thanks

p45cal
02-23-2015, 08:33 AM
What does
?Clng(Sheets("Main").Range("C6").Value)
give?
I'm thinking that your date maybe a string rather than a real Excel date (a number).

rohan8510
02-23-2015, 09:05 AM
hi thank you so much:) i got your point yes it was a string - the date, just one question when i had written this

?"]" & "CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx" & "[" and pressed enter i got the desired result
]CNAV IACB 20150218.xlsx[

just wanted to know what it did, i mean was it debug because in your post no.10 at last you said to enter

Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate this was the same one when i had first input before contacting you.

p45cal
02-23-2015, 11:18 AM
[/B]Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate this was the same one when i had first input before contacting you.
Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate << mine
Windows ("CNAV" & "" & "IACB" & "" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").Activate) << yours. Note the last close parentheses position (and the inclusion of .xlsx).

After some experimentation here it seems that even though the date is a string, Format seems to interpret it, although in some cases, depending on your locale, it might get the month and day mixed up if the date string is of the format 12/11/2015 which could be interpreted as 11 December or the 12th of November.

So you're all sorted now?

rohan8510
02-24-2015, 02:15 AM
Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate << mine
Windows ("CNAV" & "" & "IACB" & "" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").Activate) << yours. Note the last close parentheses position (and the inclusion of .xlsx).

After some experimentation here it seems that even though the date is a string, Format seems to interpret it, although in some cases, depending on your locale, it might get the month and day mixed up if the date string is of the format 12/11/2015 which could be interpreted as 11 December or the 12th of November.

So you're all sorted now?

yes, Thank you so much for your help:) i have started to learn so much from you regarding vba :)

rohan8510
02-25-2015, 03:49 AM
hi p45, i have this code every time it says run time error 1004 application defined or object defined error. I am trying to run another macro in another workbook from my current workbook. many thanks :)



Workbooks("DummyIACB.xlsm").Application.Run& "macro1"

p45cal
02-25-2015, 04:13 AM
try:

Application.Run "DummyIACB.xlsm!macro1"

rohan8510
02-25-2015, 05:09 AM
try:

Application.Run "DummyIACB.xlsm!macro1"[/CODE



hi p45 i tried this but it says run time error 1004 application defined or object defined error. i am actually trying to call the macro in another workbook named DummyIACB.xlsm and it is placed in "This workbook" in the file DummyIACB.xlsm.the name of the file from which iam trying to call is CNAV IACB 20150218.xls many thanks

iam trying to call
[CODE]
Sub macro1()
Sheets("Sheet1").Activate
Range("a40").Select

End Sub

p45cal
02-25-2015, 06:13 AM
try:
Application.Run "DummyIACB.xlsm!ThisWorkbook.macro1"

rohan8510
02-25-2015, 07:57 AM
Thanks so much this worked :) you are in uk? isnt it? by the way my name is Rohan 31 year old male from city Kolkata in India.

rohan8510
03-06-2015, 01:32 AM
hi pk, this code i have i am not able to figure out why it says object variable or with block variable not set could you please take a look, many thanks



Windows("CNAV.xlsm").Activate
Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks("DummyIACB.xlsm").Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats

rohan8510
03-06-2015, 01:37 AM
hi pk, this code i have i am not able to figure out why it says object variable or with block variable not set could you please take a look,
the error pops at third line, when i close all the workbooks and open only the workbook which has this code it works fine, many thanks



Windows("CNAV.xlsm").Activate
Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks("DummyIACB.xlsm").Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats

rohan8510
03-06-2015, 01:48 AM
hi p45, this code i have i am not able to figure out why it says object variable or with block variable not set could you please take a look,
the error pops at third line, when i close all the workbooks and open only the workbook which has this code it works fine,
i have tried to add Dim rngcopy As Object but it did not work, many thanks



Dim rngcopy As Object
Windows("CNAV.xlsm").Activate
Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks("DummyIACB.xlsm").Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats

rohan8510
03-06-2015, 01:54 AM
hi p45, this code i have i am not able to figure out why it says run time error 91 "object variable or with block variable not set" could you please take a look,
the error pops at third line, when i close all the workbooks and open only the workbook which has this code it works fine,
i have tried to add "Dim rngcopy As Object" but it did not work, many thanks



Windows("CNAV.xlsm").Activate
Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks("DummyIACB.xlsm").Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats