saving range to variable.

[rl23455]

Hi,
Real green programmer. using VB Excel.
I ultimately am taking range strings and exporting to html format straight into a website page. I got the excel spreadsheet to open and it creates a webpage, but I do not get data from the spreadsheet.

I've tried

Range("A3:B3").Select
BeforeImprovement = ActiveCell.Value
No sooner than I write this and the above code works. go figure. do I have to close the module to get it to save properly?
and just

AfterImprovement = Range("C3:D3").Select

but the strings never show up in the webpage.
Also, I have true and false values for "easier" cells, and I can get true pasted in the webpage, but not the word easier.
If (Range("D11").Select = "true") Then Safer = "Safer" Else Safer = ""

all the code:



Sub Macro2()

Static BeforeImprovement As String
Dim AfterImprovement As String
Dim Effect As String
Dim DiscipineDept As String
Dim ProjectNumber As Long
Dim Initiator As String
Dim Reviewer As String
Dim DateSubmitted As Date
Dim Safer As String
Dim Easier As String
Dim MoreInteresting As String
Dim LessRelearning As String
Dim LessRework As String
Dim MoreValue As String
Dim MoreProductive As String
Dim strHTML As String
Dim mypagename As String

Set workbookname = Workbooks.Open(filename:="C:\Documents and Settings\3441\My Documents\wiki\mindtouch\excelexports\QnEK_Pierce.Kenny_29FEB08.xls")
mypagename = workbookname.Name
Debug.Print "opened workbook"
workbookname.Activate

Range("A3:B3").Select
BeforeImprovement = ActiveCell.Value
AfterImprovement = Range("C3:D3").Select

Effect = Range("B4").Select
DisciplineDept = Range("D5").Select
ProjectNumber = Range("d6").Select
Initiator = Range("D7").Select
DateSubmitted = Range("B8").Select
Reviewer = Range("D8").Select
If (Range("D11").Select = "true") Then Safer = "Safer" Else Safer = ""
If (Range("D12").Select = "true") Then Easier = "Easier" Else Easier = ""
If (Range("D13").Select = "true") Then MoreInteresting = "More Interesting" Else MoreInteresting = ""
If (Range("d14").Select = "true") Then LessRelearning = "Less Relearning" Else LessRelearning = ""
If (Range("d15").Select = "true") Then LessRework = "Less Rework" Else LessRework = ""
If (Range("d16").Select = "true") Then MoreValue = "More Value" Else MoreValue = ""
If (Range("d17").Select = "true") Then MoreProductive = "More Productive" Else MoreProductive = ""
Debug.Print "got data from cells, starting to create html"
strHTML = "<table cellspacing='1' cellpadding='1' border='1' style='width: 100%'> "
strHTML = strHTML & "<tbody style='vertical-align: top'>"
strHTML = strHTML & "<tr> <td colspan='2' style='text-align: center'><font style='font-size: 28px'>Kaizen Report</font></td></tr>"
strHTML = strHTML & "<tr> <td>Before Improvement:  I had this problem.</td> <td>After Improvement: I made this change.</td> </tr>"
strHTML = strHTML & " <tr style='background-image: none; vertical-align: top; text-align: left'> <td>"
strHTML = strHTML & "<p>" & BeforeImprovement & "</p>"
strHTML = strHTML & "<p>      </p>"
strHTML = strHTML & "<p>      </p>"
strHTML = strHTML & "<p>      </p></td>"
strHTML = strHTML & "<td>" & AfterImprovement & "</td> </tr>"
strHTML = strHTML & "<tr> <td colspan='2'>"
strHTML = strHTML & "<p> The Effect: It got a little better. " & Effect & "</p>"
strHTML = strHTML & " </td> </tr>"
strHTML = strHTML & "<tr> <td> Check all that apply:  </td></tr>"
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td>" & Safer & "<br>" & Easier & "<br>" & MoreInteresting & "<br>" & LessRelearning & "<br>" & LessRework & "<br>" & MoreValue & "<br>" & MoreProductive & "<BR></td>"
strHTML = strHTML & "<td> <p> Discipline/Dept: <br />" & Discipline & "</p> </td></tr>"
strHTML = strHTML & "<tr> <td> </td><td> Initiator:; " & Initiator & "</td></tr>"
strHTML = strHTML & "<tr><td> Date Submitted:; " & DateSubmitted & "</td><td> Reviewer: " & Reviewer & "</td></tr>"
strHTML = strHTML & "</tbody></table>"
' post to page call mypostsite function
Debug.Print "call mypostsite function"
mystatus = mypostSite(mypagename, "Disciplines_and_Departments/Lean_Promotion_Office/QnEK_Quick_and_Easy_Kaizen/", "130.77.189.51", strHTML, "tkline.user", "idcuser")
Debug.Print mystatus

workbookname.Close SaveChanges:=False
End Sub
Public Function mypostSite(mypage As String, mypath As String, myServer As String, pagetext As String, myUserName As String, myPassword As String) As String
'myPage = The name of the page you want to create, case sensitive. ex: AAAAAA
'myPath = The path you want the page in, with trailing /. ex: Servers/
'myServer= The server name you want to post to w/o http:. ex: myserver.com or 10.1.1.1
'pageText= The html body of your page. ex: Testing 123
'myUser= The user name you want to use to create the page
'myPassword= The password for the above user.

Dim strcookies As String
Dim xmlreq32 As Object

'Steps:
'1. Get a cookie
'2. Use the cookie
'3. Send the informaton
'4. Clean up

'This replaces the / with the URI Equivilant
mypath = Replace(mypath, "/", "%252f")

'1. Get a cookie and save it to strcookies...can be reused stored in cookies, like a regular webpage
strcookies = getCookieAuth(myUserName, myPassword, myServer)

'Uses buillt in http connection via Internet Explorer
Set xmlreq32 = CreateObject("Microsoft.XMLHTTP")

'Create a new page
xmlreq32.Open "POST", "http://" & myServer & "/@api/deki/pages/=" & mypath & mypage & "/contents/?dream.in.verb=POST", False
'Because of the way Microsoft works, this must be set twice
'http://support.microsoft.com/kb/234486
xmlreq32.setrequestheader "Cookie", strcookies
xmlreq32.setrequestheader "Cookie", strcookies

'Set our formating
xmlreq32.setrequestheader "Content-Type", "text/plain"

'msgbx pagetext

'Send the page...Similar to Curl -H
xmlreq32.send (pagetext)

'Return our status...for debugging information
mypostSite = "Status: " & xmlreq32.statustext & " Code: " & xmlreq32.Status & " Response: " & xmlreq32.responsetext
'Code 200 = Successful
'Code 403 = Bad username/password
'Code 400 = Page exists, need edittime...see above to uncoment line

'Clean up to be ready for next time.
Set xmlreq32 = Nothing
End Function
Public Function getCookieAuth(myUserName As String, myPassword As String, ServerName As String) As String
Dim httpAddress, tmpCookieAuth As String
Dim xmlreq As Object

' Create the website information
httpAddress = "http://" & myUserName & ":" & myPassword & "@" & ServerName & "/@api/deki/users/authenticate"

' Create an xmlhttp object:
Set xmlreq = CreateObject("Microsoft.XMLHTTP")

' Opens the connection to the remote server.
xmlreq.Open "POST", httpAddress, False

'Actually Sends the request and returns the data:
xmlreq.send

' Send the cookie authentication code to a variable
tmpCookieAuth = xmlreq.responsetext

' Clear the object we created
Set xmlreq = Nothing

' Return our cookie as a string
getCookieAuth = tmpCookieAuth
End Function

 

[GlennUK]

Before trying to decipher your code, you need to understand that Range("d6").Select doesn't return the value of the cell d6, it returns true ( usually ) to indicate that the Select command worked. Use Range("d6").Value to access the value of a cell.

 

[rl23455]

Yeah, I figured that out, and so my others work, but still this does not as these are different, they come from a checkbox. I tried both ways below. Does it matter if the cells are hidden?
thanks

Range("D11").Select
If (ActiveCell.Value = True) Then Safer = ("Safer") Else Safer = ("")
Debug.Print "safer value"; ActiveCell.Value

Range("D12").Select
If (Range("D12").FormulaR1C1 = "true") Then Easier = "Easier" Else Easier = ""

 

[GlennUK]

Try not selecting, rather just reference cells directly, as in

If Range("D11").Value = True Then 
    Safer = "Safer" 
Else 
    Safer = ""
End If

or even

If Range("D11").Value Then 
    Safer = "Safer" 
Else 
    Safer = ""
End If