VBA corrupting Cell A1 in CSV file

M

Mark

I'm not a profesional programmer but I know enough to mess stuff up! I have
a form that will be filled out and there is a submit button on the bottom.
On submit the information is passed to a CSV file. This is my first time
trying to pass info to a CSV. When ever I submit the form, the cell a1 on
ends up being 255 # signs. I'll attach my code, I'm hoping someone can shed
some light on thi for me.
------------------------------------------------------------------------------------------
Private Sub CommandButton2_Click()
'Submits information entered into form to master escalation list

Application.ScreenUpdating = True

Dim workingdir As String
Dim Workingfilename As String
Dim workinglocation As String
Dim escnum As String
Dim cnt As String
Dim RngMax As Variant

workingdir = "\\rci.rogers.ca\dfsdata\rss\bu 03\resp 7077\dept
200\Leadership Team\Cable Specific\Mark's Templates & Reports\Escalation
Project\"
Workingfilename = "master.csv"
workinglocation = workingdir & Workingfilename

If IsFileOpen(workinglocation) = True Then

MsgBox ("The file is in use, wait a moment and try again.")
Exit Sub

Else

'On Error Resume Next

Workbooks.Open (workinglocation)

'Application.Wait (Now() + TimeValue("0:00:05"))

a = 2
cnt = 1

If Cells(1, 1) <> "" Then

Do While ActiveSheet.Cells(a, 1) <> ""
a = a + 1
cnt = cnt + 1
Loop

End If

RngMax = WorksheetFunction.Max(Range(Cells(1, 1), Cells(cnt, 1)))

escnum = 20080000 + cnt

If escnum < RngMax Then
escnum = RngMax + 1
End If

Cells(a, 1) = escnum

Cells(a, 2) = Now()
Selection.NumberFormat = "d-mmm-yy h:mm;@"
Cells(a, 3) = CxName
Cells(a, 4) = AcctNum
Cells(a, 5) = PriNum
Cells(a, 6) = AltNum
Cells(a, 7) = CBTime
Cells(a, 8) = Dept
Cells(a, 9) = askfs
Cells(a, 10) = FSName
Cells(a, 11) = EscDets
Cells(a, 12) = "Pending"

'escmemo = Cells(a, 1)

Workbooks("master.csv").Close (True)

Escalation_Form.Hide

MsgBox ("Your Escalation has been sent " & "Escalation number: "
& escnum)

End If

End Sub
 
M

Mark

Tried that and I'm still getting the same thing. There i already a value in
that cell - the new record that is being added is going down to the next
blank row - which could be thousand of rows down - could there be an issue
with the way the file is being opened/closed?
 
M

Mark

Still getting the same thing - could there be an issue with the way the file
is being opened/closed?
 
J

Joel

Found another probelm. You dont need the @ and the select cells is not
specified.

from
Selection.NumberFormat = "d-mmm-yy h:mm;@"

to
Cells(a,2).NumberFormat = "d-mmm-yy h:mm;"
 
J

Joel

You also need to make escnum a double because of the size of the number

from
Dim escnum As Long

to
Dim escnum As Double
 
M

Mark

I had to set 3 of the variables as double and that seems to have worked!
Thanks a lot Joel!! I really appreciate your help! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top