Worksheet References Help needed

Hi folks,

I seem to get hung up on passing the worksheet references into Macros in order to get them to work..despite thining i've sussed how they work ( i do have plenty of macros where they do, and i've used them sucessfully) I keep getting hung up on some of them...

I now have these two Macros where I need to pass in appropriate worksheet references in order to be able to get the macros to run when called..

Code below...

Any help gratefully recieved.. thanks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Cheers Joseph, I tried your code and it seemed to mess up the dates, which were origionally working... using the original code,

I've now played about with the original code a little and tried mixig it with some of the stuff you provided giving this:

Please Login or Register  to view this content.

If i run this on an individual sheet, it works no problems at all, the trouble is that when I call it as a procedure after calling the others it doen't do the job... it formats the dates, but doesn't change them round....

THis is the code that's running the other proceudres (macros)

Please Login or Register  to view this content.

There are 20 worksheets being pulled into the workbook and then the various macros are being called to run elements of the data cleanup, Any ideas on how I can get this one to work? Cheers

yeah, it's def column E... its really odd as it works fine on one worksheet in isolation and i've triple checked the column references. Got me stumped, not sure if it has something to do with the way its looping through the Calls, but I have tried changing the order in which they are called (miking sure the column reference reflects any changes - and still its doesn't work..

real pain as the date, correctly, is critical...

Hi, still having the same issues with this file - and its driving me nuts.... the column reference is correct correct accross all the sheets so its not that. Can anyone think of another way to solve the date issue?

31/05/2013 16:45:56
06/03/2013 12:01
06/03/2013 13:30

Here the 31st May has imported okay but the 3rd of June has been transposed... i've spent days trying to sort this, surely there must be an easier way!!

Thanks

yeah,

Here it is... i've changed some of the data for obvious reasons!

Due to the file size I;ve had to delete a number of the sheets here, there are normally 20 of them, all containing data in the same formats and in the same column positions!

The Convert date Macro does run in isolation and does what its supposed to do, but when its being called and runnig through the loop on all sheets it looks like its failing... i.e not switching the Month and Date round... driving me mental!

Cheers guys!

Norie, Am I missing something? - its not liking this at all...not changed any of the dates as I would expect...

the Macro is calling the text to columns proc quite happily, but then not the convert dates one.... just so you can see that i'm not going mad, here's how the code has been copied across...

Please Login or Register  to view this content.

Sub Text_to_Column_Comma(sh As Worksheet)
'

Please Login or Register  to view this content.

Please Login or Register  to view this content.

that last section of code doesn't actuyallu have the line in it "Surely this shouldn't be as hard as it seems!"

and reads

Please Login or Register  to view this content.

I'm not having a good day!

Norrie, You've maxed out your messages...

there's too many files... 20 files a day, unless you mean as part of the open / import macro? you think it would work?

hadn't thought of that, and to be honest, probably out of my depth with it...fumbling in the dark, using what I know to get this thing to work, begging, borrowing and stealing code from who and where I can

okay so that worked for one worksheet... here's the macro....

Please Login or Register  to view this content.

and the current code for importing them all....

Please Login or Register  to view this content.

fancy helping me combinging the two!?

sorry... here's the the code with the filepath in it..

Please Login or Register  to view this content.

when it runs it's giving the following error message in the mSGBOX "Excel cannot find the data to refresh this external data range, check to make sure the text file has not moved or been renamed."

It hasn't... has it got something to do with the code here

Please Login or Register  to view this content.

looking for the worksheet name that in theory has been passed in? I'm lost

sure... debugged to here... .Refresh BackgroundQuery:=False

Please Login or Register  to view this content.

odd!

so a quick search says that its a problem with that line... remove it and.... it runs, also removed the reference to the control sheet!

The plot thickens now tho.... it's imported 20 wsheets, all of them are blank!

so because the workbooks are all blank it makes it quite difficult to see whats going on!

i'm guessing because there are 20 sheets being added into the original workbook the code is looping through to a degree...

The original marcro had a sheetname in this line here

With ws.QueryTables.Add(Connection:= _
"TEXT;" & sFile, Destination:=ws.Range("$A$1"))
.Name = "sheetname"
.FieldNames = True

interestingly enough on the workbook its just naming the blank sheets from 4-24 (1-3) already in the open workbook, so its not naming the sheets with the file name... I tried naming to "sFile" and playing about... code still ran, but didn't make a difference...

I cant get my head round why the data would be blank though and not pulling through anything! at least the code isn't failing

yeah file names would be good... still using this code

Please Login or Register  to view this content.

still deosn;t explain why the data is blank, the files are loaded with data

thanks for all ur help today! appreciate it!

here's a sample file, again in isolation this opens okay, running with various macros etc its messed up!

Hi Norie, I;m working with the extracts our DBA's have given me.... i';m assuming the extracts are from an SQL query which automatically puts the entire line in in double quotes in order to give me the csv file that was requested...

the extract comma in Flags Response probably comes from the fact that there are additional fields in the DB that we didn't request so they have cut the extract at that point and it has a comma in it where the DB would cotinue on with the extra fields!

Can you think of a smart way to remove the double quotes, from all 20 files before running your code above?

so reading this

http://en.wikipedia.org/wiki/Comma-separated_values It looks like I'm stuck with them!

I've tried a couple of other things this morning, doing a find and replace on the original file and saving, all very labourious and time consuing, even cause my system to crash, saving as a text file....

I'me beginning to wonder if, reverting to the origional code, i.e trying to solve getting the date to split and concatenate properly would be a better idea? Any thoughts!?

i'm guessing its the way the DBA's have created the files... changing them now isn't an option i'm out of DBA time and budget so I need to try and work with what i've got! Thanks again!

Norie your a star, have just done a happy dance round the office - well nearly but not quite!

This works almost perfectly - the code bugs out on the sheet naming, some of the file names are longer than 31 chars... when i comment out this line

Please Login or Register  to view this content.

it seems to run fine...

this wasn't an issue on the original code, I've tried putting back in the error handlers to see if that would help, and had a look at the original code to see how that was handling it but can't see it doing anything special, any suggestions.. i'm frighted i screw up the code, and because of the number of sheets I do need the sheet names..

Thanks again

okay - so i'm some way there with this...

I've declared a second variable.... albeit probably in the wrong way and using it in the wrong places... This is allowing the code to run and renaming the sheets... it's doing half of them then throwing up an invlaid procedure call or argument when it gets about half way through! Anyone any thoughts>? Will heep plugging away... cheers

Please Login or Register  to view this content.

doh..... rabbit and a warren... was easier than I thought, not the tidiest of solustions but I've just taken the 30 left most characters of the the String and ditched all the other rubbish!

Please Login or Register  to view this content.

This works fine...

Thanks again, will no dount have more questions as we go along... This code works perfectly, and from all the reading I have done seems to cause a lot of fold major issues... You deserve major credit for solving this!

The fileneame is the only unique identifier - none of the data in the file has this! I can use Sfile elsewhere and the sheetname now that I have it... its not the tidiest as its 30 chars using

Please Login or Register  to view this content.

but its a start!

Sfile2 - was me going down the wrong road and trying to protect the good work you had done by not screwing up anything else!

yeah that would work too, i'm not getting too hung up on it for now - just glad to have some working code and useable dates , although i'm now back to fumbling around with passing worksheet references in and out of code and tring to get it to work!

Norie... hate to re-open an old thread and might open a new one depending on your thoughts?

Given that you managed to solve the import problem for the files and getting the dates to import correctly from the files this problem is exactly the same. One of the columns the "subject" in the file contains a string which is dash "-" delimited.

It either looks like this

(06/11) - Test Sub 1 (Location1) - S Name1 - Action1 **MIDART** - Completed - User Name1- 11/06/2013

Or like this

(06/11) - Test Sub 1 (Location1) - S Name1 - Action1

without the string **MIDART** - Completed - User Name1- 11/06/2013
appended to it..

Currently i'm running a series of Macros's to split this data out:

1st one to split the subject into two parts - one the left part anything before **MIDART** and the other the right part anything after **MIDART**

Please Login or Register  to view this content.

and then another macro - a simple text to columns, using the dash as a delimiter on the right part so i can work on the separate columns contained in the **MIDART** - Completed - User Name1- 11/06/2013

Please Login or Register  to view this content.

For whatever reason, the text to columns on the Right part of the subject is doing the same as beofre with the dates, converting some to dates, some to strings.

I have copied and pasted the caluclated columns from the macro above as values, so its not calulating on forumlas. I have also trued trimming out any spaces but to no avail. I had thought it was something similar to before, but i'm stumped... any thoughts?

so for anyone Interested and reading this.... turns out the solution was pretty straight forward, and by playing about managed to figure it out!

It was a matter of properly defining the Array Sequence and values in which the columns were imported...

Heres the code for anyone interesyed

Sub Text_Columns(ws As Worksheet)
'
'

Please Login or Register  to view this content.