|
VBA corrupting Cell A1 in CSV file
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 |
VBA corrupting Cell A1 in CSV file
this is your problem
from Dim escnum As String to Dim escnum As Long "Mark" wrote: 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 |
VBA corrupting Cell A1 in CSV file
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? "Joel" wrote: this is your problem from Dim escnum As String to Dim escnum As Long "Mark" wrote: 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 |
VBA corrupting Cell A1 in CSV file
Still getting the same thing - could there be an issue with the way the file
is being opened/closed? "Joel" wrote: this is your problem from Dim escnum As String to Dim escnum As Long "Mark" wrote: 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 |
VBA corrupting Cell A1 in CSV file
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;" "Mark" wrote: Still getting the same thing - could there be an issue with the way the file is being opened/closed? "Joel" wrote: this is your problem from Dim escnum As String to Dim escnum As Long "Mark" wrote: 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 |
VBA corrupting Cell A1 in CSV file
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 "Joel" wrote: 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;" "Mark" wrote: Still getting the same thing - could there be an issue with the way the file is being opened/closed? "Joel" wrote: this is your problem from Dim escnum As String to Dim escnum As Long "Mark" wrote: 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 |
VBA corrupting Cell A1 in CSV file
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! :) "Joel" wrote: 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 "Joel" wrote: 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;" "Mark" wrote: Still getting the same thing - could there be an issue with the way the file is being opened/closed? "Joel" wrote: this is your problem from Dim escnum As String to Dim escnum As Long "Mark" wrote: 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 |
| All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com