Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

Mr. Apollo: Nov 8, 2000

jusion posted:

Is Google Docs an option?

No, unfortunately not.

# ? May 18, 2012 14:35

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 16:57

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Sonic H posted:

My brain has given up on this one. Hopefully a genius here can help.

Basically I'm trying to create a dynamic, dynamic named range. That is to say, I'm trying to create a dynamic named range where the (single) column in which the range sits is itself dynamic. The usual method for dynamic ranges
code:
=OFFSET($D$6,0,MATCH(Cover!E6,Platform_Dropdown,0),COUNTA(D:D),1)
isn't working as the COUNTA(column:column) is the bit that needs to be dynamic. Counting which column to use is easy - that's the MATCH bit.

I'm essentially trying to create a set of linked dropdowns, second of which automatically changes based on the first one. Currently I've got a table of headings (the 1st dropdown - Platform_Dropdown, a named Range) and the options for the 2nd list as rows under each heading.

The killer is I can't use any VBA.

I'm open to other suggestions on how to do this too - but no VBA (not my choice).

Thanks.

Edit: Problem solved although if there alternative ways of doing things, I'd be happy to hear them.

You can make the criteria of the count another offset.

code:

=OFFSET($D$6,0,MATCH(Cover!E6,Platform_Dropdown,0),COUNTA(OFFSET(D:D,0,"Column Criteria Here")),1)

# ? May 18, 2012 14:54

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Mr. Apollo posted:

Not really. The people who will be using it only have Excel.

You could use Access to enter the data, then have queries that pull out the data you need for each spreadsheet with the formulas. Then each time you add new data to the main table you run the queries and export as Excel for the recipients.

# ? May 18, 2012 14:57

Dial M for MURDER: Sep 22, 2008

I am trying to compile a list of people in the company, and the dates of when their various certifications expire. When I enter the dates I want to have an entry made in the outlook calendar for a reminder to have them renew their certs. I have some code I found on that will add the date I list in column A and the person in column B. The problem is that whenever I run the macro, it duplicates the calendar entries. Here is the code that I am trying to use.

code:


Sub ScheduleAppts()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appt As Outlook.AppointmentItem
Dim R As Integer
Dim X As Integer

R = Range("A65536").End(xlUp).Row

Set ns = ol.GetNamespace("MAPI")
Set olFolder = ns.GetDefaultFolder(olFolderCalendar)

For X = 1 To R
Set appt = olFolder.Items.Add
With appt
.Start = Sheets("Sheet1").Cells(X, 1).Value
.Location = Sheets("Sheet1").Cells(X, 2).Value
.Save
End With
Next X

Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub

Are there few lines I can throw in to make it check for an entry of the same name on the same date before adding it to my calendar, or do I need a whole new code? Thanks

Dial M for MURDER fucked around with this message at 20:52 on May 18, 2012

# ? May 18, 2012 16:10

Johnny Cache Hit: Oct 17, 2011

Excel is starting to make me really angry.

My wife has a spreadsheet she is using to track shelf-life testing she is performing on some samples. She marks the name & the date they started testing. The samples are tested monthly for a year.

To complicate things, they want to pull samples every Monday morning - so if a sample went into testing May 1, it would be pulled the week of May 28 - June 1.

All I want to do with Excel is to display the samples that should be tested this week:

code:

Week of May 14 - May 18:

Sample 1 | Added Apr 16, 2012
Sample 5 | Added Dec 12 2011
...
Sample poo | Added May 20, 2011

How the heck do I even begin to think about this in Excel?

# ? May 18, 2012 19:03

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Kim Jong III posted:

stuff...

In your example, May 28th starts a 7 day period where the majority of that week is in May. You need to find out if the company counted that week as May because the majority of it was in May or just because the first of the week was May.

So, would July 30th - August 5th be counted as the last week of July or first week of August?

Assuming it would be counted as the last week of July, the following formula would work to flag samples to be pulled this week...

code:

=IF(AND([@Start]<TODAY()-WEEKDAY(TODAY())+2,MONTH([@Start])=MONTH(TODAY()-WEEKDAY(TODAY())+2),YEAR([@Start])=YEAR(TODAY()-WEEKDAY(TODAY())+2)),"Y","")

Today that would yield the following results

code:

Sample Name | Start      | Active this week?
ABC         |  5/12/2011 | 
DEF         |   5/5/2012 | Y
AEC         |  5/10/2012 | Y
DBF         |  5/15/2012 |
ADF         |  5/20/2012 |

# ? May 18, 2012 22:24

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Dial M for MURDER posted:

Excel -> Outlook

I'm sure there's a way to check for an existing meeting, but I don't deal with Outlook much. Instead here's a way to prevent duplicating meetings within Excel using a second sheet.

code:

Sub ScheduleAppts()
	Dim ol As New Outlook.Application
	Dim ns As Outlook.Namespace
	Dim olFolder As Outlook.MAPIFolder
	Dim appt As Outlook.AppointmentItem
	Dim R As Integer
	Dim RNG as range	
	Dim X As Integer
	Dim vSTART as date
	Dim vLOCATION as string
	dim vNEW as worksheet
	dim vOLD as worksheet

	set vNEW = thisworkbook.sheets("Sheet1")
	set vOLD = thisworkbook.sheets("Sheet2")

	R = vNEW.Range("A65536").End(xlUp).Row

	Set ns = ol.GetNamespace("MAPI")
	Set olFolder = ns.GetDefaultFolder(olFolderCalendar)

	For X = 1 To R
		vSTART = vNEW.cells(X, 1).value
		vLOCATION = vNEW.cells(X, 2).value

		if application.worksheetfunction.countifs(vOLD.range("A:A"),vSTART,vOLD.range("B:B"),vLOCATION)=0 then
			Set appt = olFolder.Items.Add
			With appt
				.Start = vSTART
				.Location = vLOCATION
				set rng = vOLD.range("A65535").end(xlup).offset(1,0)
				rng.value = vSTART
				rng.offset(0,1).value = vLOCATION
				.Save
			End With
		end if
	Next X

	set rng = nothing
	set vOLD = nothing
	set vNEW = nothing
	Set ol = Nothing
	Set ns = Nothing
	Set appt = Nothing
End Sub

# ? May 18, 2012 22:44

Swink: Apr 18, 2006; Left Side <--- Many Whelps

I've got a dumb request from a client that I cant solve.

He wants to track a loan repayment, where the repayment amount can vary, as well as the date he pays. Each time a payment is made, he wants to add on the interest for the days since the previous payment.

He wants to know exactly how much is left on the loan at any given time. He intends to pay semi-regularly with some large lump payments but also some periods where he pays nothing.

I cant figure out how to keep a running total of the balance outstanding. I need to add x days of interest to the balance, and subtract the principal paid each time a payment is made. He intends to update the sheet himself with the date paid and the amount.

I've not worked with finance stuff in Excel, and this request sounds pretty stupid, but if someone could take a quick look and where I'm at so far I'd appreciate it.

http://dl.dropbox.com/u/58703/runninginterest.xlsx

# ? May 22, 2012 09:33

G-Dub: Dec 28, 2004; The Gonz

Swink posted:

I've got a dumb request from a client that I cant solve.

He wants to track a loan repayment, where the repayment amount can vary, as well as the date he pays. Each time a payment is made, he wants to add on the interest for the days since the previous payment.

He wants to know exactly how much is left on the loan at any given time. He intends to pay semi-regularly with some large lump payments but also some periods where he pays nothing.

I cant figure out how to keep a running total of the balance outstanding. I need to add x days of interest to the balance, and subtract the principal paid each time a payment is made. He intends to update the sheet himself with the date paid and the amount.

I've not worked with finance stuff in Excel, and this request sounds pretty stupid, but if someone could take a quick look and where I'm at so far I'd appreciate it.

http://dl.dropbox.com/u/58703/runninginterest.xlsx

At a glance, in F6 you want "=F5 - J6" and then fill it down that column. I think you have all the component parts correct.

Do you want the top-left to display the balance as of the current date?

# ? May 22, 2012 19:31

Swink: Apr 18, 2006; Left Side <--- Many Whelps

Yeah but the top left is unimportant.

Thanks - I guess I'd been staring at it too long and I'd lost all my sense.

# ? May 23, 2012 01:27

Dial M for MURDER: Sep 22, 2008

Old James posted:

I'm sure there's a way to check for an existing meeting, but I don't deal with Outlook much. Instead here's a way to prevent duplicating meetings within Excel using a second sheet.
code:
GREAT STUFF

Thank you so much, although I am an idiot when it comes to VBA, so how am I supposed to use the sheets now?

Dial M for MURDER fucked around with this message at 16:10 on May 23, 2012

# ? May 23, 2012 16:02

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Dial M for MURDER posted:

Thank you so much, although I am an idiot when it comes to VBA, so how am I supposed to use the sheets now?

Sheet1 is the existing list of reminders to create. Sheet2 are reminders already created. When the macro runs it check Sheet2 to see if you had previously created the reminder, if the meeting info is on Sheet2 it doesn't create a new reminder.

So unless this would only prevent duplicates going forward (unless you took the time to figure out what meetings were already scheduled) and if the user deletes the reminder this will not recreate that meeting.

# ? May 23, 2012 16:30

Dial M for MURDER: Sep 22, 2008

I put the code in it's own module and filled in column "A1" with a date/time and "B1" with a name on sheet1. When I go tools -> macro-> run I get and error that says

code:

Run-time error '438':
Object doesn't support this property or method

I run debug and it highlights this line

code:

 If Application.WorksheetFunction.countifs(vOLD.Range("A:A"), vSTART, vOLD.Range("B:B"), vLOCATION) = 0 Then

# ? May 23, 2012 17:16

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Dial M for MURDER posted:

I put the code in it's own module and filled in column "A1" with a date/time and "B1" with a name on sheet1. When I go tools -> macro-> run I get and error that says
code:
Run-time error '438':
Object doesn't support this property or method
I run debug and it highlights this line
code:
 If Application.WorksheetFunction.countifs(vOLD.Range("A:A"), vSTART, vOLD.Range("B:B"), vLOCATION) = 0 Then

Try changing

code:

Dim vSTART as Date

code:

Dim vSTART as String

# ? May 23, 2012 17:33

Dial M for MURDER: Sep 22, 2008

I have it in there now as

code:

Sub ScheduleAppts()
    Dim ol As New Outlook.Application
    Dim ns As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim appt As Outlook.AppointmentItem
    Dim R As Integer
    Dim RNG As Range
    Dim X As Integer
    Dim vSTART As String
    Dim vLOCATION As String
    Dim vNEW As Worksheet
    Dim vOLD As Worksheet

    Set vNEW = ThisWorkbook.Sheets("Sheet1")
    Set vOLD = ThisWorkbook.Sheets("Sheet2")

    R = vNEW.Range("A65536").End(xlUp).Row

    Set ns = ol.GetNamespace("MAPI")
    Set olFolder = ns.GetDefaultFolder(olFolderCalendar)

    For X = 1 To R
        vSTART = vNEW.Cells(X, 1).Value
        vLOCATION = vNEW.Cells(X, 2).Value

        If Application.WorksheetFunction.countifs(vOLD.Range("A:A"), vSTART, vOLD.Range("B:B"), vLOCATION) = 0 Then
            Set appt = olFolder.Items.Add
            With appt
                .Start = vSTART
                .Location = vLOCATION
                Set RNG = vOLD.Range("A65535").End(xlUp).Offset(1, 0)
                RNG.Value = vSTART
                RNG.Offset(0, 1).Value = vLOCATION
                .Save
            End With
        End If
    Next X

    Set RNG = Nothing
    Set vOLD = Nothing
    Set vNEW = Nothing
    Set ol = Nothing
    Set ns = Nothing
    Set appt = Nothing
End Sub

But I seem to be getting the same error, and it highlights the same line as before

# ? May 23, 2012 20:27

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Dial M for MURDER posted:

I have it in there now as

But I seem to be getting the same error, and it highlights the same line as before

It's basically complaining with the format of the countifs() when it is using a date as a criteria. We may need to include it within "". Try

code:

        If Application.WorksheetFunction.countifs(vOLD.Range("A:A"), """" & vSTART & """", vOLD.Range("B:B"), vLOCATION) = 0 Then

EDIT: Oh, are you running this macro within Outlook? I thought it was running in Excel and then calling Outlook. the line it is erroring on is Excel specific since it is calling an Excel function.

Old James fucked around with this message at 02:05 on May 24, 2012

# ? May 23, 2012 20:31

Dial M for MURDER: Sep 22, 2008

Still has the same error and highlights that line. Is there an something in I have to reference manually besides the Outlook library?

# ? May 23, 2012 21:32

G-Dub: Dec 28, 2004; The Gonz

Are you using 2007? Countifs is only available in 2007 onwards

# ? May 24, 2012 07:16

Dial M for MURDER: Sep 22, 2008

That's the problem, I have 2003. Sorry I should have stated I have an antique version.

# ? May 24, 2012 23:03

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

This is more of a 'wouldn't it be nice' kind of thing. I've got a lady I'm helping out who is doing pay per click campaigns. I've pulled a bunch of stuff out of Google Keywords via API into a database, and then given her a spreadsheet. There's a unique ID, a company name, and a keyword. These may be repeated as there could be multiple keywords per uniqueid/keyword. What she does is decide which keyword is 'best' by putting an 'x' into a column. So what she ends up with is a sheet that looks like:

code:

ID  |  Name     | Keyword      | Selected |
10  | Joe's Art | Joe's Art    | NULL     |
10  | Joe's Art | Art New York | X        |
12  | AA SexToyz| AA SexToyz   | NULL     |
12  | AA SexToyz| SexToyz NY   | X        |
12  | AA SexToyz| Butt Plugs   | NULL     |

The problem arises is that she's sending me back the sheets to 'double check' that:
a: There is at least one 'x' per uniqueID
b: There is not more than one 'x' per uniqueID
c: A list of all 'selected' rows, with the NULL selected rows removed

This is incredibly easy for me in my SQL database, which I accomplish like so:

code:

Case a:
SELECT * FROM Keywords_temp WHERE Selected IS NULL AND UniqueID NOT IN
(SELECT UniqueID FROM Keywords_temp where Selected='x')

Case b:
SELECT UniqueID from Keywords_temp WHERE Selected='x' 
GROUP BY UniqueID Having COUNT(*)>1

Case c:
SELECT * FROM Keywords_temp WHERE Selected='x' AND
UniqueID NOT IN
(SELECT UniqueID from Keywords_temp WHERE Selected='x' 
 GROUP BY UniqueID 
 HAVING COUNT(*)>1)

The problem being, all this data is in the sheet I sent her, but I'm trying to think of an easy way that she can formula up the same results without having to come back to me. The initial dataset was provided as part of a contract, but all this post hoc support was not. The sheet has about 50,000 rows. Does anyone know of an Excel solution that would let me do what I'm doing in SQL above? You don't have to make a full blown example, I'm pretty experienced in these things.

EDIT-Also, she can't connect to my DB directly via ODBC or anything like that. She's using Excel 2010.

# ? May 25, 2012 01:55

Aredna: Mar 17, 2007; Nap Ghost

I would make a default pivot table off of the data (select the columns with your data, Insert, Pivot Table, OK)

Put the ID (or Name if it's also unique) as your Row Label
Put Selected as your Column Label
Put Selected as your values
Sort the column under X either descending - anything with more than 1 is at the top, anything with 0 is at the bottom.

Since we selected all of the columns in the first step when she wants to refresh this all she needs to do is paste the data on top of what is there currently and then go to the pivot table, right click on it, and select refresh.

You could also do something with countifs (2007 or 2010) or an array formula (2003), but it will end up with more complicated steps for her to update it each time meaning it's easier for her to make a mistake and need your support again.

# ? May 25, 2012 16:00

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Thanks, I can see how that would work. I'd been stuck in the 2003 world so long the only thing I could think of would have been tonnes of countifs and named ranges and so on, and just didn't want to go down that road.

# ? May 28, 2012 01:03

Swink: Apr 18, 2006; Left Side <--- Many Whelps

I'll preface this with the fact that I dont know the first thing about databases and SQL, or if this will help you at all, but I bookmarked this post because it seemed useful at the time - http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/

From what I can tell its a very basic instruction on setting up your sheet to accept SQL statements (via VBA).

# ? May 28, 2012 04:54

SvK: Feb 20, 2010

Scaramouche posted:

Thanks, I can see how that would work. I'd been stuck in the 2003 world so long the only thing I could think of would have been tonnes of countifs and named ranges and so on, and just didn't want to go down that road.

How automated do you want the double check to be? Shouldn't be to hard to write a VBA function that can do this.

# ? May 31, 2012 01:30

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

This person is pretty... not smart with Excel (though great at online marketing) so I didn't want to complexify things with allow scripts to run/popups etc. I like the elegance of the pivot as well, it worked out fine.

# ? May 31, 2012 02:44

SvK: Feb 20, 2010

Scaramouche posted:

This person is pretty... not smart with Excel (though great at online marketing) so I didn't want to complexify things with allow scripts to run/popups etc. I like the elegance of the pivot as well, it worked out fine.

Good thing you replied quickly I was like 3/4's the way done with a macro to do it. With dynamic arrays and countif's =).

# ? May 31, 2012 02:49

ZanderZ: Apr 7, 2011; by T. Mascis

I have a question regarding Google Spreadsheet. Not sure if there's a thread for that, but if there is, I haven't seen it.

I'm trying to create a faux hyperlink that takes you to another sheet. For instance, I have a main sheet with a list of names. If I click the cell with the name "Bill" in the cell, I wanna be taken to the sheet titled "Bill"

Is this possible in Excel? I'd rather do it in Google docs, but I'd be willing to do it in Excel if Google Docs isn't an option.

ZanderZ fucked around with this message at 21:53 on Jun 5, 2012

# ? Jun 5, 2012 21:45

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

ZanderZ posted:

I have a question regarding Google Spreadsheet. Not sure if there's a thread for that, but if there is, I haven't seen it.

I'm trying to create a faux hyperlink that takes you to another sheet. For instance, I have a main sheet with a list of names. If I click the cell with the name "Bill" in the cell, I wanna be taken to the sheet titled "Bill"

Is this possible in Excel? I'd rather do it in Google docs, but I'd be willing to do it in Excel if Google Docs isn't an option.

In Excel when you create a hyperlink you can have it take you to a cell on another spreadsheet. Not sure about Googledocs.

# ? Jun 6, 2012 05:56

ZanderZ: Apr 7, 2011; by T. Mascis

Old James posted:

In Excel when you create a hyperlink you can have it take you to a cell on another spreadsheet. Not sure about Googledocs.

Ok, I'm using Excel then.

So I've got the following in Sheet "Master" Cell B2 which displays foobar.

=CONCATENATE('Jon'!B2&'Jon'!C2)

Sheet "Jon" cell B2 has "foo" written in it, and cell C2 has "bar" Is there any way to have it do a line break, so I get...

foo
bar

or better yet, double spaced...

foo

bar

instead of foobar?

\/ Thanks! Turns out it's CHAR(13) for Excel Mac 2011

ZanderZ fucked around with this message at 19:19 on Jun 6, 2012

# ? Jun 6, 2012 14:28

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

When typeing in a cell alt+enter puts a line break in the text or you can use char(10) so either of the two below would work

code:

=CONCATENATE('Jon'!B2&"
"&'Jon'!C2)

code:

='Jon'!B2&CHAR(10)&'Jon'!C2

And you can do multiples to get the double space.

EDIT: When using the CONCATENATE formula you separate the values with commas. Using the ampersand symbol is an alternate way of combining strings without using the CONCATENATE formula so it is redundant in your examples.

Old James fucked around with this message at 23:48 on Jun 6, 2012

# ? Jun 6, 2012 17:26

ZanderZ: Apr 7, 2011; by T. Mascis

Ok, so this concatenate formula is getting pretty long. How can I use something that's better for scripting than the formula bar? I wanna be able to space all this poo poo out like an actual script as opposed to a formula.

I wanna be able to have something like...

=CONCATENATE
(
'Joe'
!B2
&CHAR(13)
&CHAR(13)
&

'Joe'
!C2
&CHAR(13)
&CHAR(13)
&

'Joe'
!D2
&CHAR(13)
&CHAR(13)
&

'Joe'
!E2
&CHAR(13)
&CHAR(13)
&CHAR(13)
&

'Joe'
!F2
&CHAR(13)
&CHAR(13)
)

As opposed to one big line of text. Is this possible? This is my first time doing anything in excel that isn't simple algebra. Right now, with the one line formula bar, it takes longer to copy and paste with a trackpad than it does to just type it out.

ZanderZ fucked around with this message at 22:40 on Jun 6, 2012

# ? Jun 6, 2012 22:35

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Check out my edit above, you were misusing the '&' within CONCATENATE. Your code should look like this.

code:

=CONCATENATE('Joe'!B2,CHAR(13),CHAR(13),'Joe'!C2,CHAR(13),CHAR(13),'Joe'!D2,CHAR(13),CHAR(13),'Joe'!E2,CHAR(13),CHAR(13),CHAR(13),'Joe'!F2,CHAR(13),CHAR(13))

Though I am not sure why you want those last two char(13)'s since there is no text after them.

Also, if you are dealing with a long formula you can expand the size of the formula bar by dragging the bottom edge down.

# ? Jun 6, 2012 23:53

ZanderZ: Apr 7, 2011; by T. Mascis

Old James posted:

I am not sure why you want those last two char(13)'s since there is no text after them.

Also, if you are dealing with a long formula you can expand the size of the formula bar by dragging the bottom edge down.

I figured out the extra char(13)'s and removed them from the formula. I know about expanding the formula bar by dragging the bottom edge down.

What I'm referring to is how difficult it is to make multiple edits to a big, long, line of text. Right now, I'm writing my formulas in Text Wrangler so I can go back and quickly edit, line by line. Then I have to go back, remove all the spaces and line breaks and coppy/paste it into the formula bar.

# ? Jun 7, 2012 19:36

Capri Sunrise: May 16, 2008; Elephants are mammals of the family Elephantidae and the largest existing land animals. Three species are currently recognised: the African bush elephant, the African forest elephant, and the Asian elephant.

Does anybody have any experience with fixing compatibility issues? I don't know how to program but I am working on an Excel spreadsheet that will open .pdf files on my company's intranet and take them to a specific page. I have a code that works on most of the firm's computers (Excel 2003 SP3 with Windows XP.

code:


Sub AppendixCSnowData()
Dim IE As Object, strFile As String, iPageNum As Long
strFile = "P:\Structural - Misc\NBCC 2005\National Building Code of Canada 2005 Volume 2.pdf"
iPageNum = 233
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate strFile & "#Page=" & iPageNum
IE.Visible = True
End Sub

This works, however on other's computers running either new versions of Excel or the same one but with Windows 7, this code does not work and gives an error on the "IE.Navigate strFile & "#Page=" & iPageNum" portion. I have been unable to find anything online that will work on new versions of Excel. Opening .pdf files works, however going to a noted page does not on the other OS set-ups.

# ? Jun 11, 2012 01:38

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Wilhelm posted:

Does anybody have any experience with fixing compatibility issues? I don't know how to program but I am working on an Excel spreadsheet that will open .pdf files on my company's intranet and take them to a specific page. I have a code that works on most of the firm's computers (Excel 2003 SP3 with Windows XP.
code:
Sub AppendixCSnowData()
Dim IE As Object, strFile As String, iPageNum As Long
strFile = "P:\Structural - Misc\NBCC 2005\National Building Code of Canada 2005 Volume 2.pdf"
iPageNum = 233
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate strFile & "#Page=" & iPageNum
IE.Visible = True
End Sub
This works, however on other's computers running either new versions of Excel or the same one but with Windows 7, this code does not work and gives an error on the "IE.Navigate strFile & "#Page=" & iPageNum" portion. I have been unable to find anything online that will work on new versions of Excel. Opening .pdf files works, however going to a noted page does not on the other OS set-ups.

When you open the pdf maually within IE on those other machines and go to a page is the URL different? Perhaps the #page= is no longer the correct syntax in later versions. Just a guess.

# ? Jun 11, 2012 02:46

coyo7e: Aug 23, 2007; by zen death robot

I was asked about helping someone parse a string of text to pull out some numbers for calculations. The complication here, is that the source strings are typed in by hand by any shmuck, so the number (distance) to pull out may be in "0.12mi", "2.34 mi", "20,3km", or "103,47 km".. Or maybe even with a couple spaces instead of just one. I've never hosed with mixed-up commas and periods being used for decimals, and am wondering if there's anything in Excel which can handle mixed up decimals?

I'm thinking it's probably most prudent to nip it in the bud by replacing all commas ahead of time?

coyo7e fucked around with this message at 18:44 on Jun 21, 2012

# ? Jun 21, 2012 18:41

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

coyo7e posted:

I was asked about helping someone parse a string of text to pull out some numbers for calculations. The complication here, is that the source strings are typed in by hand by any shmuck, so the number (distance) to pull out may be in "0.12mi", "2.34 mi", "20,3km", or "103,47 km".. Or maybe even with a couple spaces instead of just one. I've never hosed with mixed-up commas and periods being used for decimals, and am wondering if there's anything in Excel which can handle mixed up decimals?

I'm thinking it's probably most prudent to nip it in the bud by replacing all commas ahead of time?

That's how I'd approach it as well, try and standardize the comma/period formatting first. I'd eyeball to make sure there weren't any weird big number switcheroos though, like 100,000.00 or 100.000,00, before running a find and replace.

DukAmok fucked around with this message at 19:00 on Jun 21, 2012

# ? Jun 21, 2012 18:58

mattdev: Sep 30, 2004; Gentlemen of taste, refinement, luxury.

Women want us, men want to be us.

DukAmok posted:

That's how I'd approach it as well, try and standardize the comma/period formatting first. I'd eyeball to make sure there weren't any weird big number switcheroos though, like 100,000.00 or 100.000,00, before running a find and replace.

I'm the one he's referring to. This is the task that I've been given and I'm having a TON of trouble with it. Here's what the data looks like:

https://docs.google.com/spreadsheet/ccc?key=0Am5EbI_l2pbcdFZfdFlGNUIxZ01qNDNBak5wTmlTQmc&pli=1#gid=0

The items in purple is what I'm trying to parse out of each individual message. I need the mileage (with kilometers normalized to miles), the pace (also normalized to miles) and any #tag that isn't #tag1. The tricky part is that all of these messages are different, some of them have commas as decimals, and some have no space between the distance and mi/km. It's kind of a nightmare.

This is the excel formula that I have to pull the first number out, but it only works on some of them. Any ideas?

code:

=REPLACE(LEFT(B4,LOOKUP(10,MID(B4,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(B4&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0

# ? Jun 21, 2012 20:29

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

mattdev posted:

I'm the one he's referring to. This is the task that I've been given and I'm having a TON of trouble with it. Here's what the data looks like:

https://docs.google.com/spreadsheet/ccc?key=0Am5EbI_l2pbcdFZfdFlGNUIxZ01qNDNBak5wTmlTQmc&pli=1#gid=0

The items in purple is what I'm trying to parse out of each individual message. I need the mileage (with kilometers normalized to miles), the pace (also normalized to miles) and any #tag that isn't #tag1. The tricky part is that all of these messages are different, some of them have commas as decimals, and some have no space between the distance and mi/km. It's kind of a nightmare.

This is the excel formula that I have to pull the first number out, but it only works on some of them. Any ideas?

Couple questions: does distance always come before rate? Does rate always have a slash after it? Is there always only one tag aside from tag1? I can see a simple VBA script to knock most of this out, PM me if you want to take it offline.

# ? Jun 21, 2012 21:26

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 16:57

mattdev: Sep 30, 2004; Gentlemen of taste, refinement, luxury.

Women want us, men want to be us.

Yep, distance always comes before rate but rate doesn't always have a /. Most of the time it does, but sometimes it doesn't. Rate also sometimes doesn't exist, but that's rare.

I will shoot you a PM.

# ? Jun 21, 2012 21:28

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »