Copy specific columns from an external worksheet to current workbook

Hi

I am trying achieve the following using VBA (must be VBA as it is part of a larger process).

Copy columns A:B in 'Sheet1' of external worksheet 'SourceData.xlsx' to the same columns in existing worksheet 'Upoad' in the the current workbook 'Master' (where all the VBA exists).

The data in 'Sheet1' of 'SourceData' has been created by a 3rd party application so we have no control over its format: there is text in column A and a date/time stamp in column B.

No user prompts or message boxes are required. This seems like a fairly straight forward process as it is always the same columns of data coming from the same file to the same destination.

I am not a VBA specialist so would welcome any kind of assistance.

Many thanks

Here's my go at things:

I would create a folder for the external doc, because I like to know where my toys are. I'll call it 'SourceFolder' here. Put the SourceData workbook in the SourceFolder each time, then run the following code from your 'Master' workbook:

(N.B.: My program is designed to delete 'SourceData' when it's done, clearing the folder. If you don't want that, then exclude the noted line below)

Please Login or Register  to view this content.

Hi. Thanks for this - I will test it out tomorrow.

I won't delete the file, but I will copy it to an archive folder with a time/date stamp appended to the name, then I will clear the content of Sheet1 so it can be re-used. I think I can do this as I have done it before on another app - its just a case of copying the code over and making the necessary changes.

Thanks again for your help - I will let you know how I get on.

Hi

I tried your code, and it works OK to a point. I get the dreaded 'Run-time error 1004, Application-defined or object-defined error'.

This occurs when it tries to execute "rng.SpecialCells(xlCellTypeConstants).Select".

I single-stepped through the code and it opens the data file OK but then it stops at the above command.

I have slightly modified your code to target a specific file as there will also be a control file (.xlsx) in the same folder. I did, however, test your code before making any changes (other than putting the correct folder name in). Your code is also preceded by a user prompt asking if they want to update this data to start with.

My code is as follows.

Please Login or Register  to view this content.

I hope you can help with this.

Many thanks and regards.

Forgot to include the 'End Sub' in the code snippet.

Are there any merged columns in SourceData? Perhaps the time/date stamp?

Hi.

No, there are no merged columns.

A brief bit of information about the Excel file.

It was created as an empty file for dumping records in to. We have a barcode reader that works on Wi-Fi. One of its features is that it can send the barcode data over Wi-Fi to a designated file on a server. This is the file it ends up in.

The output from the barcode app can be in either CSV or Excel format. Sending it in CSV compacts it into a single field per row, which is difficult to separate as the first field is the customer number and barcode number and they are of a variable length. Also, the app does not give any flexibility on the CSV format (eg deliimiters). The older version of this program worked well and it was pulling in a CSV file happily, but I don't understand enough about it to modify the code.

Another option I thought of was to copy the worksheet in in its entirety to a new sheet (eg DataIn), then copy the data from the DataIn worksheet to the Upload worksheet. Then, delete the DataIn worksheet. This may be simpler.

Your thoughts would be appreciated.

Regards

Tom

In truth, copying with "select" commands is an ill-advised practice. I shouldn't be so lazy. 'Select' has a few advantages, but it tends to be slower and less predictable. A possible alternate route would be to replace:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

Assuming there's no unwanted data in SourceData...

The other alternative would be to replace:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

The non-VBA users I work with seem to like the 'select' method, because it's easier for them to find and edit the range in the code on their own. More seasoned folks prefer this last method because it's the most efficient (and fastest to run).

Hi

Sorry not to have got back to you before, but I have been working on other projects.

I had a go at my solution of just copying in the whole worksheet and that seemed to work, but thank you very much for your efforts - they are appreciated.

Regards