Get data from file name that changes...

[ChrisXcel]

I've got all my VBA macros set up and working.

I just need to add one more step.

Since all the activity is going on in FileA I think it makes sense to keep the VBA code in that file.

To get things started I need to copy data from FileB into FileA onto worksheet NewData.
The problem is that FileB's name will always be changing!

It will always start with 'xl00000' but will have different numbers after that and end with .xls

What code could I use to copy the data between these two files?

Opening the two files manually is no problem.

[AlphaFrog]

Have the code prompt the user to select a file (workbook B) and assign a workbook variable to it when the code opens it.

Please Login or Register  to view this content.

There are different methods to copy data between the two workbooks. Here is one. Thisworkbook refers to the workbook that contains the code...

Please Login or Register  to view this content.

[Marc L]

Or if the file is alone starting with the prefix in the folder then just use the VBA function Dir with a wildcard …

[ChrisXcel]

Have the code prompt the user to select a file (workbook B) and assign a workbook variable to it when the code opens it.

Please Login or Register  to view this content.

There are different methods to copy data between the two workbooks. Here is one. Thisworkbook refers to the workbook that contains the code...

Please Login or Register  to view this content.

Two problems right now with the code.
It says file is 'not in recognizable file format'.
And it is not finding the right directory, despite my stating the dir explicitly in the chdir path.
So I had to burrow down to find the file and open it.

The easiest thing for me to do right now is to open the two .xls files (workbooks) manually.
From there I'd like to run the code from the main file.
All it has to do is refer to the other workbook that is open, to get data from it.

[ChrisXcel]

Another reason it's not good to go hunting for the file through code, is because the file that needs to be opened is an .xlsx file
When that file is opened, my version of Excel automatically converts it to an .xls file with a completely different name!
The name is xl00000....xls

[AlphaFrog]

This will reference the 2nd file that you opened manually regardless of is name...

Workbooks(2)

[AlphaFrog]

Another reason it's not good to go hunting for the file through code, is because the file that needs to be opened is an .xlsx file
When that file is opened, my version of Excel automatically converts it to an .xls file with a completely different name!
The name is xl00000....xls

I haven't seen Excel do that. I use Excel 2003.

[ChrisXcel]

This will reference the 2nd file that you opened manually regardless of is name...

Workbooks(2)

Thanks AlphaFrog that did the trick!

Just one more thing.

I put in the following code:

Please Login or Register  to view this content.

The data currently goes until G10600, but it expands each day.
I put G20000 to cover expansion.

Is there an easy way to have it get the actual range where the data is on Sheet2?
Then I wouldn't have to keep watching that all the data was collected.
Also how would I put this reference in the NewData sheet?

Chris

[AlphaFrog]

Please Login or Register  to view this content.

[ChrisXcel]

Please Login or Register  to view this content.

Thanks very much Alpha, that worked out nicely!

[ChrisXcel]

Hi Alpha, I spoke a bit too soon it seems.
When I first tried your code I got an error.

Then it worked. I'm not sure what I changed.

But now it's been three times in a row that it gets hung up in the same place:

Runtime error '9'
Subscript out of range.
LastRow = Workbooks(2).Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

I am making sure that I have the two files open, and ready to go.
Any ideas?

[AlphaFrog]

Try this...

LastRow = Workbooks(2).Sheets("Sheet2").Range("A65536").End(xlUp).Row

[ChrisXcel]

Try this...

LastRow = Workbooks(2).Sheets("Sheet2").Range("A65536").End(xlUp).Row

Thanks again Alpha for your help!

That didn't make a difference, but I think I found out what was causing the problem.
I wasn't referring to the sheets explicitly enough it seems, so Excel was getting confused.
I am now using Workbooks(1).Sheets, which I wasn't before:

Please Login or Register  to view this content.

I split up your lines:

Please Login or Register  to view this content.

putting one at the top, and inserting a couple of lines of my code (the above ones) in between.
But as I mentioned, now my code is explicitly stating which workbook to use.

I also added an explicit line a little later (Workbooks):

Please Login or Register  to view this content.

I've now ran this code at least five or more times without a hitch so I'll keep my fingers crossed
that it won't crash anymore.

[ChrisXcel]

Sorry to have to open this thread up again...

I thought all was good.

It happens at the start of a new day when I get a fresh data file.

I run my VBA code and it gets hung up on the same line at the top of my code

Please Login or Register  to view this content.

I get this error:
Runtime error '9'
Subscript out of range

Now get this.

If I run it a few times in a row, I get the same results. Hangs up on the same line.

But...

If I close the data file, and open a data file from four days ago, it works!
Then when I close that file and open a data file from three days ago, it works!
Then when I close that file and open a data file from two days ago, it works!
Then when I close that file and open today's data file once again, this time it works!!!!!

Then I re-run the VBA code six or more times on today's file and it works without fail each time!

Does anyone have any idea what is going on here?

[ChrisXcel]

Well today I tried to trick/outsmart the macro.

Instead of opening the new data sheet immediately, I opened a couple of old ones first and ran the macro on them.
The macro ran perfectly with no errors.

Then I opened the new data file and ran the macro. It ran just fine!

So I think I will use this method going forward, perhaps running it on just one set of old data, rather a few sets.

[ChrisXcel]

Well today I tried to trick/outsmart the macro.

Instead of opening the new data sheet immediately, I opened a couple of old ones first and ran the macro on them.
The macro ran perfectly with no errors.

Then I opened the new data file and ran the macro. It ran just fine!

So I think I will use this method going forward, perhaps running it on just one set of old data, rather a few sets.

I'm curious to know if anyone has an idea what could be causing my VBA code to fail on the line mentioned two posts above?
It fails only if it's run on the latest data file (today's) FIRST.

If I run the VBA code on the data file from yesterday, first, and then try to run today's data file, there are no errors and everything works fine.

The data files are constructed exactly the same, the only difference is that new data is added to the file, so file keeps getting bigger.

So for example if I have 10 data files numbered 1-10, and 10 is the latest, if I try to run the VBA code with the 10 data file, first thing in the day, it will fail with the error.
I can run the VBA code with any of data files 1-9 to start the day and each one will run perfectly.

And of course as I said, after I have run the VBA code with any of data files 1-9, 10 will then work without the error.

[AlphaFrog]

Can you attach your macro file and a data file that errors.

[Marc L]

I'm curious to know if anyone has an idea what could be causing my VBA code to fail on the line mentioned two posts above?

This kind of codeline fails each time as this VB style coding is not valid under VBA ‼
Just see in VBA help how to declare a variable using Dim statement …

[ChrisXcel]

This kind of codeline fails each time as this VB style coding is not valid under VBA ‼
Just see in VBA help how to declare a variable using Dim statement …

Hi Marc, thanks for helping.

I cut the line of code that was failing from the declared lines above it.
I am declaring the variables properly.

[ChrisXcel]

Can you attach your macro file and a data file that errors.

Hi Alpha.

Today I decided to be brave and try to run the VBA code on today's data file first.
I hadn't tried in over a week.
Today it worked!

I think it's the first time it has worked. Pretty crazy.

Anyway, I'll mark the thread as solved for now and keep my fingers crossed.
If it acts up in the coming days I'll post the files.

Thanks!

[ChrisXcel]

Hi Folks,

I just wanted to do a followup to this post, because I learned something new.

I learned why sometimes the VBA would fail on the second line of code:

LastRow = Workbooks(2).Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row


It was simple really.

If I opened the data file first, it didn't work!
That's because the code is expecting Workbooks(2) to be the data file.

And if I opened the data file first then Workbooks(2) becomes the file with the VBA code!

It's been rock solid now for months.