sheet reference in vba 2010 vs. 2007...need a hand with this pls

[jayd4wg]

i have a macro that pulls data from a spreadsheet and writes a google earth KML file...it was working fine until IT decided that instead of installing Access 2007 on my machine, they updated my full office suite and my macro no longer works.

the original spreadsheet is 270k lines and i'm not a fan of using spreadsheets as databases, so this will eventually get moved to access (thus the request to install access) but i'm under the gun to produce some map files for some of the data now. i don't have a machine available to me with excel 2007 to just run the macro i have...so i need to fix the macro quickly.

Sub generateFullKML()
'
'Generate KML Macro
'Written by Jason Basham
'For internal use only by Windstream Communications Employees
'Not for use by the technically inept.

'dim variables
Dim pm_name$, pm_addr_st$, pm_addr_2$, pm_city$, pm_state$, pm_zip$, pm_acctNum$, pm_facClass$, pm_parentAcctNum$, pm_renewDate$, pm_contactName$, pm_contactNum$, pm_expDate$, pm_term$, pm_autoRenew$, pm_facEqv$
Dim pm_dyIP As Double
Dim pm_revenue As Long
Dim pm_lat$, pm_lon$
Dim cell As Range
Dim i As Long
Dim pm_style As String
Dim user_filenm As String



i = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'establish filepath and open document for writing

user_filenm = InputBox("Please enter a filename without drive letter or path", "INPUT FILENAME")
    If user_filenm = "" Then
    End
    Else
        'filepath = "C:\MyMap.kml"
        filepath = "C:\zzzMapItProj_" & user_filenm & ".kml"
    End If
    
Open filepath For Output As #1

'write header to file and create StyleMap for KML output.

outputHeader

'loop through rows of data

For Each cell In [Data!a5:a279999]
    If cell.EntireRow.Hidden = False Then
        pm_name = cell.Offset(0, 6)

obviously, there is much more to the macro, but the line that i'm failing on is the "for each cell in [Data!"... I get an error "object required" which tells me that 2010 is handling the sheet names differently and I have to define the sheet as an object, and possibly even the range as an object as well?

I have to do something with the worksheet names in 2010...i've tried just using activesheet to no avail...i can't quite get the syntax right. Thanks in advance for any help you may be able to lend here.

 

[Jonmo1]

What is the actual problem?
do you get an error? what error?
does it give the wrong result? What result DOES it give, what result did you expect?

My first attempt would be to change
For Each cell In [Data!a5:a279999]
to
For Each cell In Sheets("Data").Range("a5:a279999")

Not to say the way you did it is 'wrong', but when in doubt, do it the most explicit way instead of using the shortcut.

 

[jayd4wg]

ah...i had the sheet name without quotes when i tried it explicit. now i have other issues, but i can work thru those. general Type Mismatch errors...i can work these out. THANK YOU!

 

[Jonmo1]

You're welcome.

 

[jayd4wg]

okay, this one is kicking my butt.

I have pm_lat dim'd as a string, also tried it as a double to no positive effect.
when it pulls "82.13456755" from the sheet to create the KML file, it gets a type mismatch error.

how the heck do you type mismatch a STRING for crying out loud?

 

[Jonmo1]

Can you post the whole relevant code?
It's probably not a mismatch with the variable and the "82.13456755"
But rather a mismatch with whatever it is you're doing with the variable.

 

[jayd4wg]

For Each cell In Sheets("Data").Range("a5:a279999")

    If cell.EntireRow.Hidden = False Then
        pm_name = cell.Offset(0, 6)

'establish values for each row


        pm_parentAcctNum = cell.Offset(0, 4)
        pm_acctNum = cell.Offset(0, 4)
        pm_contactName = cell.Offset(0, 9)
        pm_contactNum = cell.Offset(0, 10)
        pm_contactEmail = cell.Offset(0, 11)
        pm_addr_st = cell.Offset(0, 18)
        pm_addr_2 = cell.Offset(0, 19)
        pm_city = cell.Offset(0, 20)
        pm_state = cell.Offset(0, 21)
        pm_zip = cell.Offset(0, 22)
        pm_facClass = cell.Offset(0, 45)
        pm_facEqv = cell.Offset(0, 46)
        pm_revenue = cell.Offset(0, 50)
        pm_renewDate = cell.Offset(0, 41)
        pm_expDate = cell.Offset(0, 42)
        pm_term = cell.Offset(0, 43)
        pm_autoRenew = cell.Offset(0, 44)
        pm_dyIP = cell.Offset(0, 51)
        pm_lat = cell.Offset(0, 81)
        pm_lon = cell.Offset(0, 80)
        pm_name = Replace(pm_name, "&", "and")
        If pm_revenue <= 600 Then
            pm_style = "mblu_hide"
        ElseIf pm_revenue > 600 And pm_revenue <= 1200 Then
            pm_style = "mgrn_hide"
        ElseIf pm_revenue > 1200 And pm_revenue <= 2000 Then
            pm_style = "myel_hide"
        ElseIf pm_revenue > 2000 And pm_revenue <= 5000 Then
            pm_style = "morg_hide"
            Else
            pm_style = "mred_hide"
            End If
            
'catch end of document to exit loop
        If pm_name = "" Then
            Exit For
        End If

it fails on the pm_lat line.

the rest of the dim's are here:

'dim variables
Dim pm_name$, pm_addr_st$, pm_addr_2$, pm_city$, pm_state$, pm_zip$, pm_acctNum$, pm_facClass$, pm_parentAcctNum$, pm_renewDate$, pm_contactName$, pm_contactNum$, pm_expDate$, pm_term$, pm_autoRenew$, pm_facEqv$
Dim pm_dyIP As Double
Dim pm_revenue As Long
Dim pm_lat As String
Dim pm_lon As String
Dim cell As Range
Dim i As Long
Dim pm_style As String
Dim user_filenm As String

 

[Jonmo1]

The only reason I can come up with for that to give Type Mismatch is if cell.Offset(0, 81) contains an error value like #N/A or #Value! etc..

 

[jayd4wg]

bingo. good catch. i had 2 N/A's. found them by doing a quick error catch and notating the line in a msgbox. Thanks for your help!

 

[Jonmo1]

Glad to help, thanks for the feedback.