VBA code correction

[Big Lar]

What’s wrong with this code?
C:\MyCompany\Data (Sheet1) routine does not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
<o></o>

[FONT=Verdana]Private Sub CommandButton1_Click()<o:p></o:p>[/FONT]
[FONT=Verdana]Application.DisplayAlerts = False<o:p></o:p>[/FONT]
[FONT=Verdana]Application.ScreenUpdating = False<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]Dim iRow As Long<o:p></o:p>[/FONT]
[FONT=Verdana]Dim WS As Worksheet<o:p></o:p>[/FONT]
[FONT=Verdana]Set WS = Worksheets("Sheet1")<o:p></o:p>[/FONT]
[FONT=Verdana]  iRow = WS.Cells(Rows.Count, 1) _<o:p></o:p>[/FONT]
[FONT=Verdana].End(xlUp).Offset(1, 0).Row<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]WS.Cells(iRow, 1).Value = Me.TextBox1.Value<o:p></o:p>[/FONT]
[FONT=Verdana]WS.Cells(iRow, 2).Value = Me.TextBox2.Value<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]ThisWorkbook.Save<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]ChDir "C:\Company"<o:p></o:p>[/FONT]
[FONT=Verdana]Workbooks.Open Filename:="C:\MyCompany\Data "<o:p></o:p>[/FONT]
[FONT=Verdana]      Set WS = Worksheets("Sheet1")<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]iRow = WS.Cells(Rows.Count, 1) _<o:p></o:p>[/FONT]
[FONT=Verdana].End(xlUp).Offset(1, 0).Row<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]WS.Cells(iRow, 1).Value = Me.TextBox1.Value<o:p></o:p>[/FONT]
[FONT=Verdana]WS.Cells(iRow, 2).Value = Me.TextBox2.Value<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]ThisWorkbook.Save<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]Application.Quit<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]End Sub[/FONT]

<o></o>

 

[MrKowz]

Workbooks.Open Filename:="C:\MyCompany\Data "<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

This line is not opening a workbook. If your workbook is named "Data.xls", then you need to have it read:

Workbooks.Open Filename:="C:\MyCompany\Data.xls"<o:p></o:p>

Then the Set WS line should read:

Set WS = Workbooks("Data.xls").Worksheets("Sheet1")

<o></o>

 

[JackDanIce]

In this part of the code:

Workbooks.Open Filename:="C:\MyCompany\Data "<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

What is the name of the file?
Where do you define the file extension, e.g. ".xls" in your code?

 

[Big Lar]

Thanks MrKowz

That's the ticket...except this doesn't save C:\MyCompany\Data.xls
It does work in the first instance.

[FONT=Verdana]ThisWorkbook.Save[/FONT]

 

[MrKowz]

Instead of using ThisWorkbook.Save, tell VBA what specific workbook to save:

Workbooks("Data.xls").Save

 

[Big Lar]

AhHa!
Perfect...Thanks!

 

[MrKowz]

AhHa!
Perfect...Thanks!

No problem, thanks for the feedback!