Setting a workbook object with full path

[DavidSCowan]

Can someone help please.

I want to Set wkb as the workbook<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264><COLGROUP><COL style="WIDTH: 198pt; mso-width-source: userset; mso-width-alt: 8448" width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 198pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=264>C:\Users\David\Documents\EasyXL\easy-xlIndex.xls

</TD></TR></TBODY></TABLE>
I thought that:
Dim wkb as workbook
Set wkb =workbooks("<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264><COLGROUP><COL style="WIDTH: 198pt; mso-width-source: userset; mso-width-alt: 8448" width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 198pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=264>C:\Users\David\Documents\EasyXL\easy-xlIndex.xls")
would do the trick but it doesn't (and I have tried every variant I can think of)
</TD></TR></TBODY></TABLE>

What it produces is: "Run-time error '9'"; "Subscript out of range"
(I don't want to use ActiveWorkbook or ThisWorkBook)

Can someone help please.

 

[shg]

A workbook object variable refers to an open workbook, not the workbook on disk, so skip the path:

Set wkb =Workbooks("easy-xlIndex.xls")

<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=264 border=0><COLGROUP><COL style="WIDTH: 198pt; mso-width-source: userset; mso-width-alt: 8448" width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; WIDTH: 198pt; BORDER-TOP-COLOR: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #f0f0f0" width=264 height=17>
</TD></TR></TBODY></TABLE>

 

[DavidSCowan]

Great - thank you very much. And so quickly too.

 

[DavidSCowan]

Hi there

The solution:
Set wkb =Workbooks("easy-xlIndex.xls")
is great if there is only one copy of easy-xlIndex.xls on the drive

The issue I have is that in the real life problem (I only used easy-xl for simplicity) there are several copies of the file (backups etc.) with the same name on C:

Is there a way of setting the object variable that includes a path? That is to say is there a way of making the code line below work?
<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264><COLGROUP><COL style="WIDTH: 198pt; mso-width-source: userset; mso-width-alt: 8448" width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 198pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=17 width=264>C:\Users\David\Documents\EasyXL\easy-xlIndex.xls")
</TD></TR></TBODY></TABLE>
Set wkb = workbooks("C:\Users\David\Documents\EasyXL\easy-xlIndex.xls")

<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 198pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" height=17 width=264>C:\Users\David\Documents\EasyXL\easy-xlIndex.xls

</TD></TR></TBODY></TABLE>

 

[shg]

In a given Excel instance, you cannot have two workbooks with the same name open, irrespective of whether they have different paths (as they must).

What is it you're trying to do?

 

[DavidSCowan]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you very much for staying on the case. GREATLY APPRECIATED!<o></o>
<o></o>
And Sorry about the delay in getting back but, as will emerge, the problem I am having has changed slightly but is still of the same nature. This will become clear in a minute.(It has also taken some time to write this)<o></o>
<o></o>
Referring to your last note the first point to make is that I am not trying to have two files with the same name open. (incidentally I was hoping that I could do all I want to do by referencing rather than opening any of the relevant files)<o></o>
<o></o>
But first let me explain what I am trying to do:<o></o>
<o></o>
33 files have been sent to me each have five sheets (each sheet has data for three year periods, circa 2,500 rows and 40 columns). But the last 17 rows of each are incorrect. Also the whole of Sheet4 has been done on the wrong base and has to be completely replaced.<o></o>
<o></o>
Two traunches of corrections for the 33 files have been sent. The first set (33 files) have the 17 rows of data. The relevant data starts on Row 5 and finishes on Row 21. The second set (another 33 files) have the replacement for Sheet4 in the original set.<o></o>
<o></o>
Firstly I want to copy the seventeen rows of corrections into the original files i.e. copy to the final row -17. And secondly for each of the original files replace Sheet4 with the corrected sheet.<o></o>
<o></o>
I have created a new file (called ToControlTheMacroToInsertCorrections.xlsx).<o></o>
<o></o>
On Sheet 1 in Column A there are the file names including paths of the original 33 files.<o></o>
<o></o>
On Sheet 2 in Column A there are the file names including paths of the corrected 17 rows<o></o>
<o></o>
On Sheet 3 in Column A there are the file names including paths of the corrected Sheet4<o></o>
<o></o>
Without opening the files I want to copy the corrected 17 rows into the original data and replace Sheet4 as well.<o></o>
<o></o>
So I start to do the first of these tasks but get stopped in my tracks. Here is the code so far:<o></o>
<o></o>
Sub InsertingNewDataIntoWorkbooksNotUsingOpen()
Dim wkb As Workbook
Dim wkf As Worksheet 'f for the average unit price, value, volume etc. files
Dim wkss As Worksheet 'ss for the corrected sub-sectors
Dim wk05 As Worksheet '05 for the corrected tranche 2003 to 2005
Dim finalrow1, finalCol1, finalCol2, filei, fileii As String<o></o>

Set wkb = Workbooks("ToControlTheMacroToInsertCorrections.xlsx") 'Helped by Shg4421 Doesn't need a path!!
Set wkf = wkb.Sheets(1) ' this contains the file paths/names of the original data that the changes are to be pasted to
Set wkss = wkb.Sheets(2) 'this contains the file paths/names with the 17 rows to be pasted. <o></o>
Set wk05 = wkb.Sheets(3) 'this contains the file paths/names that contain the sheet to replace Sheet4. <o></o>
finalrow1 = wkf.Cells(Rows.Count, 1).End(xlUp).Row ‘finding the final row of the original data
finalCol1 = wkf.Cells(finalrow1, Columns.Count).End(xlToLeft).Column
finalCol2 = finalCol1<o></o>
For i = 2 To finalrow2
filei = wkss.Cells(i, 1).Value 'when i =2 filei this is the path and file name of the first file containing the 17 rows of corrected data
fileii = wkf.Cells(i, 1).Value 'when i =2 filei this is the path and file name of the first file containing the original data that need needs to be corrected


MsgBox filei 'Just to check if the two rows above work and they do
MsgBox fileii
'By the way when i=2 filei =C:\___1AMain\__Unilever - Deodorants\_DATA DUMP Revisiting for Macro Development Feb 2011\Corrected 2001 Data\SS avg unit price.xls
'And fileii=C:\___1AMain\__Unilever - Deodorants\_DATA DUMP Revisiting for Macro Development Feb 2011\AVG UNIT PRICE.xls<o></o>
<o></o>
'Now I WANT TO COPY THE 17 ROWS OF DATA IN filei TO THE LAST 17 ROWS IN fileii so I have written:
Workbooks(filei).Sheets(1).Cells(5, 1).Resize(17, finalCol2).Copy Destination:=Worksheets(fileii).Sheets(1).Cells(finalrow1 - 17, 1)

<o></o>
'THIS FAILS AND STOPS THE PROCESS IN ITS TRACKS<o></o>
<o></o>
THIS IS THE LINE I NEED HELP WITH<o></o>

GIVEN YOUR ADVICE EARLIER I SUSPECT IT FAILS BECAUSE filei and fileii contain the full path. But I need to give the full path for
fileii because there several files - "AVG UNIT PRICE" with this same name on C: (there are no other files wkb is set to on C: so I needn’t have given the full path here)<o></o>
<o></o>
You may be wondering what happened to the original query. The answer is that I had mistakenly thought that there was more than one ToControlTheMacroToInsertCorrections.xlsx on C: But there is only one so your solution: <o></o>
Set wkb = Workbooks("ToControlTheMacroToInsertCorrections.xlsx") works fine. <o></o>
<o></o>
As explained above the problem now is with the original data files that need their path because files of the same name exist on C: and this is what I need help with.<o></o>
<o></o>
I hope the above is clear and once again thank you very much for giving this your attention.<o></o>
<o></o>
With kind regards

David

 

[shg]

[FONT=Consolas]Workbooks(filei).Sheets(1).Cells(5, 1).Resize(17, finalCol2).Copy Destination:=Worksheets(fileii).Sheets(1).Cells(finalrow1 - 17, 1)[/FONT]

A workbook object variable refers to an open workbook ...

You have to open the files first. Then you can assign object variables to the workbooks or worksheets and go from there.

 

[DavidSCowan]

Thank you very much for your reply and sticking with it - many wouldn't!

I didn't know that to reference a workbook with a variable the said workbook needed to be open. Great to know so thank you for that.

Maybe I am being daft but I still can't this to work there does seem to be a problem with Workbooks(filei) even when filei is open

To pinpoint the problem I tried the simplist test.

Earlier in the macro I have opened filei using:

Workbooks.Open Filename:=filei

And when i=2 filei = SS avg unit price.xls

The above works fine. SS avg unit price.xls is open

If I put Workbooks("SS avg unit price.xls").close false
then the workbook closes okay.

But if I put Workbooks(filei).close false

This produces an error

The later line:
Workbooks(filei).Worksheets(1).Cells(5, 1).Resize(17, finalCol2).Copy Destination:=Workbooks(fileii).Worksheets(1).Cells(finalrow1 - 17, 1)

doesn't stand a chance if Workbooks(filei) and Workbooks(fileii) aren't working.

I have also tried surrounding filei with various combinations of speech marks and ampersands but to no avail.

What am I doing wrong?

Thanks again.

David

 

[shg]

But if I put Workbooks(filei).close false this produces an error

I think we're back to where we started, David. You reference the workbook by name, excluding the path.

You are not referring to the workbook on disk, you are referring to the workbook in memory.

 

[DavidSCowan]

Yes indeed - we are back to the same sort of isse we started with - sorry! (I was working on the idea that the same file with a different path is a different file)

However having corrected this mistake, and having spent a considerable amount of time trying everything I can think of, the operation of copying and pasting the 17 rows of corrected data into the original data sent still doesn't work.

I created two new variables fileia and fileiia. So when i=2 we now have:

filei=
<TABLE style="WIDTH: 798pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1064><COLGROUP><COL style="WIDTH: 798pt; mso-width-source: userset; mso-width-alt: 34048" width=1064><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 798pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=1064>C:\___1AMain\__Unilever - Deodorants\_DATA DUMP Revisiting for Macro Development Feb 2011\Corrected 2001 Data\SS avg unit price.xls</TD></TR></TBODY></TABLE>

fileia=SS avg unit price.xls

fileii=<TABLE style="WIDTH: 688pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=917><COLGROUP><COL style="WIDTH: 688pt; mso-width-source: userset; mso-width-alt: 29337" width=917><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 688pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=917>C:\___1AMain\__Unilever - Deodorants\_DATA DUMP Revisiting for Macro Development Feb 2011\AVG UNIT PRICE.xls</TD></TR></TBODY></TABLE>

fileiia=AVG UNIT PRICE.xls

So with both SS avg unit price.xls and AVG UNIT PRICE.xls open we get to:the line

Workbooks(fileia).Sheets(1).Cells(finalrow1 - 16, 1).Resize(17, finalCol2).Copy Destination:=Workbooks(fileiia).Sheets(2).Cells(finalrow1 - 16, 1)

But it still doesn't work. No doubt I am doing something stupid again but I can't see what it is - can you?

Thanks again for all your help in pulling retarded me up the learning curve.

With kind regards

David