|
-
Jun 16th, 2010, 03:04 PM
#1
Thread Starter
New Member
overflow error
I have an excel spreadsheet and in one column, every row contains html code. I'm trying to export the cell value that contains the code into a html file. When I run the macro created, it gives me a overflow error. Please help resolve the issue. The code is as follow:
Function SaveTextFile(strFile As String, strData As String, Optional bOverWrite As Boolean = False) As Boolean
Dim iHandle As Integer, l As Long
If Not bOverWrite Then
If Len(Dir(strFile)) > 0 Then
SaveTextFile = False
Exit Function
End If
End If
iHandle = FreeFile
l = Len(strData)
Open strFile For Binary Access Write As #iHandle Len = l
Put #iHandle, , strData
Close #iHandle
SaveTextFile = True
End Function
Function MakeString(r As Range, Optional strDelimiter As String = vbNullString) As String
Dim vArray As Variant, i As Long, j As Long, strTemp As String
If r.Count = 1 Then MakeString = r.Value: Exit Function
vArray = r.Value
For i = 1 To UBound(vArray, 1)
For j = 1 To UBound(vArray, 2)
strTemp = strTemp & vArray(i, j) & strDelimiter
Next j
strTemp = strTemp & vbCrLf
Next i
strTemp = Left(strTemp, Len(strTemp) - 2)
MakeString = strTemp
End Function
Sub Button1_Click()
Dim nrow As Long
Dim ncol As Long
Dim cellrow As Long
Dim cellcol As Long
Dim namerow As Long
Dim namecol As Long
Dim cells(cellrow, cellcol) As Long
Dim name As String
nrow = 4
ncol = 1
cellrow = 4
cellcol = 2
namerow = 2
namecol = 2
name = 1
While Not IsEmpty(Worksheets(1).cells(nrow, ncol))
Dim strMyString As String, strMyFile As String, bResult As Boolean
strMyString = "<html>" & "<body>" & cells(cellrow, cellcol) & "</body>" & "</html>"
strMyFile = "H:\desktop\" & name & ".html"
bResult = SaveTextFile(strMyFile, strMyString, True)
nrow = nrow + 1
cellrow = cellrow + 1
name = name + 1
Wend
End Sub
-
Jun 16th, 2010, 03:08 PM
#2
Re: overflow error
I don't think Len works in Binary access mode. Might as well remove that.
Which line does the error occur on? And use [code] or [highlight="vb"] brackets while you're at it.
Software I use and highly recommend: Opera, Miranda IM, Peerblock, Winamp, Unlocker Assistant, JoyToKey, Virtual CloneDrive, Secunia PSI, ExplorerXP, GOM Player, Real Alternative, Quicktime Alternative,Sumatra PDF, and non-freeware: Photoshop and VB6( ).
My codebank: AllRGB, Rounded Rectangle(math), Binary Server, Buddy Paint, LoadPictureGDI+, System GUID/Volume Serial, HexToAsc, List all processes and their paths, quasiString matching
Strings(search, extraction, retrieval etc): Retrieve BBCode Link from HTML, RemoveBetween ()'s, strFindBetween(str1,str2), Insert text in HTML, HTML - GetSpanByID
-
Jun 16th, 2010, 03:21 PM
#3
Thread Starter
New Member
Re: overflow error
strMyString = "<html>" & "<body>" & cells(cellrow, cellcol) & "</body>" & "</html>"
It works if the cell doesn't contain as much information, but I need all of the information copied into the new html file. I hope this doesn't sound too confusing.
-
Jun 16th, 2010, 03:50 PM
#4
Re: overflow error
Why would you want to put a Long type there?
That's produce output such as: <html><body>[32 bit signed number, eg -2,147,483,648 to 2,147,483,647]</body></html>
You probably want to fix that....
Another issue:
Dim cells(cellrow, cellcol) As Long 'constant expressions required, you can ReDim with these, but you can't DIM with variables for the bounds
Are you sure you're using VB6 or prior and not .NET?
Software I use and highly recommend: Opera, Miranda IM, Peerblock, Winamp, Unlocker Assistant, JoyToKey, Virtual CloneDrive, Secunia PSI, ExplorerXP, GOM Player, Real Alternative, Quicktime Alternative,Sumatra PDF, and non-freeware: Photoshop and VB6().
My codebank: AllRGB, Rounded Rectangle(math), Binary Server, Buddy Paint, LoadPictureGDI+, System GUID/Volume Serial, HexToAsc, List all processes and their paths, quasiString matching
Strings(search, extraction, retrieval etc): Retrieve BBCode Link from HTML, RemoveBetween ()'s, strFindBetween(str1,str2), Insert text in HTML, HTML - GetSpanByID
-
Jun 16th, 2010, 03:55 PM
#5
Thread Starter
New Member
Re: overflow error
I really didn't mean to put a long. I was just stuck and researching and trying things. The output is going to a html file. So I want the information to be as followed
<html>
<body>
cell reference information **which I'm using cells(rownum, colnum) to reference
</body>
</html>
cells(rownum, colnum) works for smaller data, but some cells in my spreadsheet carries a lot of text
-
Jun 16th, 2010, 04:11 PM
#6
Re: overflow error
change the savetextfile to opening a file for output
vb Code:
open myfile for output as 1 print #1, mystring close 1
if you outputs are correct you can do the other checking for overwrite etc
i don't see where you are using makestring function, but make sure your cell content does not contain any characters that may prevent /truncate writing to file
Last edited by westconn1; Jun 16th, 2010 at 04:16 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed 
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
