Select only certain columns from closed workbook

**goldfield** · 10-20-2019, 07:31 PM

Hi Gurus,
Wondering if somebody might be able to assist.

I am currently running the below code to pull data into a spreadsheet from a (dynamic) closed workbook. It is working as it should but i am hopeful that you may have a solution to only pull in certain columns. ie, if the closed workbook has 90 plus columns of data, and I only need column 2, 5, 8 and 17, then only pull those columns in.
Any help would be much appreciated as this appears to be a very resource hungry process!

Please Login or Register  to view this content.

Thanks in advance,
Regards,
Goldfield

**Leith Ross** · 10-20-2019, 07:56 PM

Hello goldfield,

This might be a case where ADO and SQL would work. But I cannot say for certain without seeing the data layout. Can you post a copy of the "closed" workbook?

**goldfield** · 10-20-2019, 08:17 PM

Hi Leith,
Thanks for the quick response.
Example attached.
Cheers.

**Leith Ross** · 10-20-2019, 10:09 PM

Hello Goldfield,

Thanks for posting the workbook. Do you have headers in row 1?

**goldfield** · 10-20-2019, 10:19 PM

Hi Leith,

Yes I do, but i removed them for confidentiality

**jindon** · 10-20-2019, 11:11 PM

This is a use of ExecuteExcel4Macro method all the way, so order should be accurate.

Please Login or Register  to view this content.

**jolivanes** · 10-20-2019, 11:39 PM

Please Login or Register  to view this content.

**jindon** · 10-21-2019, 12:24 AM

ADO
Output in ascending order by 1st column.

Please Login or Register  to view this content.

**Leith Ross** · 10-21-2019, 01:06 PM

Hello Goldfield,

Jindon's solution was close. Here is the working macro. I have installed it in the attached workbook. The results are returned to a second worksheet named "Results".

Please Login or Register  to view this content.

**goldfield** · 10-21-2019, 07:04 PM

Hi Leith,
That looks great- thanks! Just wondering if this would also work with data from a closed workbook (without opening them) and what the code might look like. There are a number of sheets that have macros to pull data from different closed workbooks.
Cheers,
Goldfield

**Leith Ross** · 10-21-2019, 07:57 PM

Hello Goldfield,

This will work with closed workbooks. Are you planning to pull data from workbooks in a single folder? If so, are the files names similar?

**goldfield** · 10-21-2019, 08:05 PM

Hey Leith- yes, they will be from a single (dynamic) folder path, with different workbook numbers in the folder. The folder path will change depending on the financial quarter and referenced by a cell (A298) in the spreadsheet- shown in code from initial message:

Quarter = Range("A298").Value

Hope this makes sense.

**Leith Ross** · 10-21-2019, 09:36 PM

Hello Goldfield,

Will you choose the quarter?

Do the workbooks have more than a single worksheet?

Will the data from each workbook be added below the previous data in the opened workbook?

**goldfield** · 10-21-2019, 10:21 PM

Hi Leith,

Yes- the quarter (cell A298) is auto-populated from a different sheet on the destination workbook. The destination workbook has multiple worksheets (with different macros on each sheet to pull relevant data).
The Source workbooks all contain 3 worksheets (Index, data1, Inquiries) but the data will always be on the sheet called "data1". Nothing is required from the Index or Inquiries sheets.
The data can over write the previous data from column 2 onwards. Column 1 is required for index match purposes. Really appreciate your help on this and I understand it makes it very difficult not having an accurate example to look at!

**Leith Ross** · 10-22-2019, 01:09 AM

Hello Goldfield,

I feel like Alice falling down the rabbit hole.

What is the date format for Range("A298") ?

Is the data in the closed workbooks arranged the same as the posted workbook, including number formats?

The data can over write the previous data from column 2 onwards. Column 1 is required for index match purposes.

Which worksheet are you referring to?
Can you post an example of what this looks like because it does not seem to match up with the workbook you posted.

**goldfield** · 10-22-2019, 02:07 AM

Hi Leith,

Sorry for any confusion. Please ignore the comment around the index match- I can sort that out at a later date, the main priority is getting the data.
To add to the confusion though...there is a formula in cell A298 = TEXT(C298,"mmmyy") which equals "Jun19".

Data and number formats are the same in both workbooks.

I will attempt to put together a dummy example.

Cheers

**Leith Ross** · 10-22-2019, 04:33 PM

Hello Goldfield,

Thanks for the additional information and putting together a sample fro me. I want to be sure about the quarterly date. It is located in the open workbook, the one running the macro, correct?

**goldfield** · 10-22-2019, 06:40 PM

Hi Leith,

Yes, the date is in the open workbook (Goldfield Copy).
Please see examples attached. I saved the "Source Sheet" in P:Path1\Path2\Jun19 and adjusted the macro on the destination sheet (Goldfield Copy workbook) to look there.
Hope this makes more sense!
Cheers

**Leith Ross** · 10-23-2019, 12:22 AM

Hello Goldfield,

Thanks for the files. That made this much easier and hopefully accurate.

The macro below will search the folder path for a workbook with the name " 'Quarter' Source Sheet.xls" where 'Quarter' is the contents of cell A298 of the Destination Sheet. The column "D,F,J" in the closed workbook will be returned to the Destination Sheet in columns "B,C,D". Previous data is deleted on the destination sheet each time the macro runs.

NOTE: Change the variable Path to folder where you files are located.

Please Login or Register  to view this content.

**goldfield** · 10-23-2019, 01:58 AM

Hi Leith,

Thanks for your persistence- it is much appreciated!
I think we are very close but i am getting "The workbook 'Source Sheet.xls' was not found in the folder:" error message. I have checked the file path to make sure that it is looking in the right spot but i can't put my finger on why it can't find it? I have tried changing directories and paths etc to no avail.

Pretty sure I have work it out now- a few tweaks here and there should get it sorted.

Thanks again for your help!

Cheers,
Goldfield

**Leith Ross** · 10-24-2019, 11:12 AM

Hello Goldfield,

Sorry I didn't get back to you yesterday. The only possible explanation I have is the path was incorrect. The quarter was added as a prefix to the file name. Is that correct or was the quarter a separate folder?

**goldfield** · 10-24-2019, 04:34 PM

Hi Leith,
The quarter is a separate folder ie P:\Path1\path2\Jun19(A298)\SourceSheet.xls, that was the issue.
Sorry if I didn't explain it clearly earlier. I then got an error with dstRng.CurrentRegion not defined, and rng.clrcontents
I managed to get it working in the sample, but have not been able to get it working in the live data as yet.
Regards,
Goldfield

**Leith Ross** · 10-24-2019, 04:42 PM

Hello Goldfield,

What issues are you having with the live data?

**goldfield** · 10-24-2019, 04:58 PM

Originally Posted by Leith Ross

Hello Goldfield,

What issues are you having with the live data?

At the moment i can't get it to find the file. In the live version there are 5 folders, the 4th being the dynamic quarter, then the file name. The file name is numeric which i wouldn't have thought would make a difference? E.g. 1234567. xls

**Leith Ross** · 10-24-2019, 05:05 PM

Hello Goldfield,

All file names are treated as strings. The same does not hold true for folders depending on the method of retrieval.

Are you getting any error messages? If so, what are they?

**goldfield** · 10-24-2019, 05:43 PM

I'm getting "The workbook '1234567.xls' was not found in the folder:P:\Path1\Path2\Path3\Path4\Jun19

**Leith Ross** · 10-24-2019, 06:39 PM

Hello Golfield,

Did you add the file separator "\" to the end of the string?

Please Login or Register  to view this content.

**goldfield** · 10-25-2019, 01:25 AM

Hi Leith,
I have now tweaked the code so it works in the live data (see attached). I now need to copy the macro (changing destination and source paths) into a number of other sheets in the same workbook. When I do that, I get a subscript out of range error (also attached). Can you please point me in the direction of where I might be going wrong, and the correct way to go about this?
Cheers,
Goldfield

Please Login or Register  to view this content.

**jolivanes** · 10-25-2019, 02:11 AM

It's late here in this part of the world. Leith might be having his beauty sleep already.
In the meantime a possibility that might cause your message.

Subscript out of range indicates that you've tried to access an element from a collection that doesn't exist. Is there a sheet "87520034" in your workbook?
No leading or trailing spaces in it's name?

And also, change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

**goldfield** · 10-25-2019, 02:28 AM

Hi Jolivanes,
Yes, the sheet "87520034" does exist. Actually- the code for the "87520034" sheet is fine, it's when I copied this to the sheet "87520033" and changed the two lines of code below that I had the issue :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I made sure there were no spaces etc. Thanks for the heads up on the other piece of code as well.
Cheers

EDIT: Sorry! I think I have been looking at this for too long...completely missed something in the file path. That is now fixed.
Now getting the below error:
Run-time error'-2147217904(80040e10)':
No value given for one or more required parameters
in this line

Please Login or Register  to view this content.

**Leith Ross** · 10-25-2019, 02:42 PM

Hello Goldfield,

With the changes you made and only showing snippets of the revised code, I am not sure what is going on now. Can you post the macro code you have that is most current?

**goldfield** · 10-25-2019, 05:19 PM

Hi Leith,

Current Code attached:

Please Login or Register  to view this content.

**Leith Ross** · 10-25-2019, 07:58 PM

Hello Goldfield,

I have added a little more error checking. Hopefully, this will help us. In the Path you were using A297. Seem to be a error so I changed it to A298.

Please Login or Register  to view this content.

**goldfield** · 10-27-2019, 06:34 PM

Hi Leith,
Thanks for the updated code. This works fine in the first spreadsheet but when I copy this code to the next spreadsheet (changing the destination and source path), I get the below error:
Run-time error'-2147217904(80040e10)':
No value given for one or more required parameters
in this line of code:

Please Login or Register  to view this content.

Can you please advise what this code does and where it might be falling over?
Cheers,
Goldfield

please ignore- this occurred as the source worksheet columns didn't exist.

**Leith Ross** · 10-27-2019, 08:36 PM

Hello Goldfield,

That line creates the ADO Record Set based on the SQL Query string. When you have problems or have made changes to the code, please post the code. I hate trying to second guess what people have done.

Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Re: Select only certain columns from closed workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy Select Columns from closed workbook into active workbook

Bookmarks

Bookmarks

Posting Permissions