Flare Sci-Fi Forums: Office Macro help (Access/Excel)

This is topic Office Macro help (Access/Excel) in forum Officers' Lounge at Flare Sci-Fi Forums.

To visit this topic, use this URL:
https://flare.solareclipse.net/ultimatebb.php/topic/10/3640.html

Posted by PsyLiam (Member # 73) on :

Okay, I'm in a shiny new job and I seem to be doing about 50 different tasks at once, as they've only just set up the centre and things are flying all over the place like so much thrown toast.

Part of the problem comes when I log calls (in something called Touchpaper). I have to write all the info down and save it, then add that information to an Access database containing all the calls we've taken for that company, and then copy it all into an Excel sheet to create a job card. The net result is that I've written the same piece of information down 3 times. I'm not expecting to be able to convert from Touchpaper to Access (since Touchpaper appears to be fairly rubbish), but surely it can't be that hard to set up a macro that takes the bottom line of a table in Excel and copies that information onto a pre-existing spreadsheet?

I know something of macros, but those have always been within one application, not across several. But then considering how interconnected Office seems to be I'm surprised that the option isn't there. Or is it and I can't find it? Please don't tell me I'm going to have to manually code in vBasic, because you might as well ask Jason to enter a spelling bee for all the success you'll get.

Posted by Cartman (Member # 256) on :

Erh, well, if you know a way to send an SQL query to an Access database from an Excel macro that does not involve doing ODBC calls in VBA (like, say, ...

code:

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:=
	Array(
		Array(_ "ODBC;DSN=MS Access Database;DBQ=C:\PrgFls\MS\OFF\DBs\Liam.mdb;DefaultDir=C:\PrgFls\MS" _),
		Array(_ "OFF\DBs;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _)
	),
	Destination:=Range("C1"))
        .CommandText = Array( _"SELECT someThing FROM someTable WHERE someConstraint" _)
        .Name = "MrSquidgee1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub




Sub Macro2()
    With ActiveSheet.QueryTables.Add(Connection:=
	Array(
		Array(_ "ODBC;DBQ=C:\WhateverYourAccessDBIsCalled.mdb;DefaultDir=C:\PrgFl\OFF\DBs;" _),
		Array(_
			"Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;
			MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransa"
		_),
		Array("ctions=0;Threads=3;UID=admin;UserCommitSync=Yes;")
	),
	Destination:= _ Range("C1"))
	.CommandText = Array(_"SELECT someThing FROM someTable WHERE someConstraint"_)
        .Name = "MrSquidgee2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

... this), then you should totally sell an image of your face to Microsoft for the next Clippy and quit your shiny new job right now, because that would be one awesome feat of awesomeness.

Posted by Sol System (Member # 30) on :

I'm pretty sure if he knew all those magic words he could just wish himself up a floating elephant and fly away to his golden mountaintop redoubt.

Posted by PsyLiam (Member # 73) on :

What he said. So we'll go for something slightly easier.

We have to log the time and date a call was logged and then assign it a severity. Depending on the severity, the time the call must be fixed changes. It was easy enough to set up a fomula that took the date and time the call was logged and add a certain amount depending on the severity, but I can't see any way for the formula to take into account the fact that it only applies for working hours and days. So, for instance, if a call was logged at 4 and it has a 2 hour fix time, it should give the fix time as being 10 the next morning rather than 6 at night. I've had a look through google and couldn't find anything.

I didn't think it was a crazy request, but apparently it is. Any ideas, people-who-do-my-work-for-free.

(Last one, I promise.)

Posted by Bones McCoy (Member # 1480) on :

You know, it may have something to do with the EPS conduits...

Posted by Cartman (Member # 256) on :

"if a call was logged at 4 and it has a 2 hour fix time, it should give the fix time as being 10 the next morning rather than 6 at night."

code:

if(call_date == a_working_day) {

  if(call_log_time < 10.00) {
    tell_caller_to_piss_off();
  }

  else if(call_log_time + call_fix_time > 17.00) {
    if(call_date != a_friday) {
      call_fix_date = call_date + 1;
    }
    else {
      call_fix_date = call_date + 3;
    }
    call_fix_hour = 10.00;
  }

}

else {
  let_phone_ring_forever();
}

Posted by Tora Ziyal (Member # 53) on :

Can't help ya with this, but good to hear you found a new job!

Posted by Lee (Member # 393) on :

I know it's possible in Access, because the very first IT job I had was using a call-logging system one of the in-house developers had built using Access.

Posted by PsyLiam (Member # 73) on :

Thanks for the help (and cheers Diane), but it doesn't really matter as they've decided not to keep me. It was only a month contract thing anyway. What confuses me is that the boss has done it because I have been late twice in that month. Once when my car broke down (phoned in and informed them, got a friend to give me a lift, arrived 20 minutes late and stayed 30 minutes after work), and another time they'd completely shut the main road from my house to work so I had to find an alternative route (about 10 minutes late, took a short lunch).

It's odd. She doesn't seem to have liked me from the beginning. Bosses either love me or seem a bit suspicious. It can't be due to not working hard enough, as I was pretty much doing everything that the person training me did after a week, and I'd completely rewritten their procedural documentation so that it was actually understandable (which they then tried to re-write, making it muddled again. Fools).

Anyway, I'm going to try doing more contract stuff. Better pay and you get variety. And stuff.

Posted by Lee (Member # 393) on :

That's strange. It seems, as you say, to have come down to personalities. I have had bosses who didn't like me, including one who spent three years gunning for me (this was at the law firm with the crap website) before I left of my own accord. Unfortunately it being a one-month contract means they're under no obligation to extend the period, and it sounds as if the boss was more interested in being able to justify her own actions (to others in the company) in not keeping you on by citing the lateness, since she can hardly claim you weren't capable of doing the work.

Just chalk it down to experience. And remember to not mention the non-extension in future job interviews - just tell them it was a one-month contract only, with no possibility of extending it. Bosses get twitchy when they hear about potential employees' problems in previous jobs, no matter how minor or unjustified. I'm assuming you're OK for references though and won't need to cite bitchboss as a reference?

Incidentally, the past coupla jobs I've done (before the one I have now) were contracts where I was taken on as one of two contractors, and in both cases I ended up as the one who wasn't the one the boss didn't like. That can have its advantages. . .