Posts by noeyedeer

Sounds like you have one book set to 1900 and one set to 1904 date systems.

Let BG explain it to you, he's richer than me :nana:

http://support.microsoft.com/d…aspx?scid=kb;en-us;180162

Cheers guys,

I'll have a look at that and see what carnage I can cause ! :bouncy:

In the VBA Project Window in the VBE all my sheets are listed and then 'This Workbook'.

What the heck is it for ????

I'm sure it must have some very handy uses but I noticed a post that referred to it the other day and have tried to find out some info but to no avail.

Can anyone give me and idea or point me in the direction of a useful link ?

Thanks in advance

Press the 'ALT' key & F11 to bring up the VBE.

Then post the code straight into there.

That should do the job for you.

You could place a =TODAY() function on your sheet and run an Auto Open macro that copies and pastes over as a value ?

:question:

Seems to have cleared itself now...... :redface:

Strange one, I think.......

I have used a sheet and exported the data and now want to delete it from within my macro.

Application.DisplayAlerts = False
Worksheets("DbkData").Delete

However I still get the standard Yes/No message even though I have turned them off.

Any ideas ??? :question:

Thanks Andy, that seems like it will do the trick (once I can decipher what's goung on !)

Every week a different set of payments will be made, sometimes 1 payment sometimes 100 so I need something pretty flexible.

Ideally I would like to only create the Data sheet when the macro is run, transfer the required info, save this sheet as a text file and then remove it again.

Looks like your code wil enable me to do all this as I wanted.

Thanks again. :thumbcoo:

That's so typical of Chris, always trying to get the last word on the subject !!

:coolwink:

Hmmm, a question for you.

I have a simple workbook, that has 2 sheets, "BANK' & 'DATA'.

On the 'BANK' sheet I have the User inputting details of companies that need to be paid. They fill in a row for each payment into fields that I have pre-formatted for each bit of required info.

After entry I want to run a macro that will pluck each bit of info from the fields in each row on "BANK' and then put them into 'DATA' as a single conctatenated entry.

My questions are:-

a) what is the best way to acheive this ? I include my current macro below that seems to cycle through the entries on 'BANK' and move them to 'DATA'. Is this the best method ??

Dim rng As Range, i As Integer
Range("C5", Range("C999").End(xlUp)).Name = "MyRange"
Set rng = Range("MyRange")
For i = rng.Rows.Count To 1 Step -1
'If rng.Cells(i).Value = "" Then
rng.Cells(i).Select
ActiveCell.Offset(0, -2).Name = "live"
Sheets("SAData").Activate
Range("A999").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = "03" & "1" & "923" & Text("AU" & Left(Range("live").Offset(0, 2)))
Sheets("SubAgent").Activate
Next

b) As you may have already seen, how the hell do I structure the functions in VB ?

ActiveCell.Value = "03" & "1" & "923" & Text("AU" & Left(Range("live").Offset(0, 2)))

The last part of my value is trying to acheive the result "AUxx" where xx is the first 2 digits of a 6 digit number. I have the function working fine is Excel but am struggling to transfer it into VB.

Hopefully yours......
:flower:

PS I am watching them scrub the 'NO WAR' slogan off of the Sydney Opera House from my office window !!

Using a Macro or just a function ?

You'll need to know what format your other application wants them in.

Then choose Save As and at the bottom of the options box there is a drop down list that will currently say Microsoft Excell Sheet.

Change this to whatever format you need (probably some sort of .txt file)

The Manual Calc should do the trick, but also have a look at the INDIRECT function in Excel help. That may also be handy in this situation.

Try using the Kill command after you have Saved As.

This will delete the file as you asked.

Kill "C:\..............."

:bisou:

Actually nearer 8.30am, we're still 11 hours ahead until the Summertimes swap in a few weeks.

I don't even attempt to answer any questions at 7am, they'd make even less sense than normal !

selection.entirerow.select

:flower:

Dam, that Chris beat me to it again !! :nana:

Hey Viper,

If you want to know more about Offset look in the below thread, Chris Davison explained it to me quite clearly.

http://www.ozgrid.com/forum/viewthread.php?tid=563

:flower:

I have now finished my swanky new macro that does everything I need it to do, and a few extra bits thanks to the suggestions on this Forum !

I just have one question re the file size. I import a fair amount of data from my banking software every month which leaves my sheet at just over 1MB. Is this file size likely to be a problem, ie unstable ?!?

:question:

Apparently MS changed the Auotsave feature in XL2002.

Have a look here for the official lowdown....

http://support.microsoft.com/d…aspx?scid=kb;en-us;289273

Hope this clears things up for ya

:question:

See your previous post.

You should only really post one topic per problem, saves people wasting time on a second answer