Export to Excel w/ VBA 1

[mca2k4]

I currently have a listbox where selected entries are exported to a brand-NEW Excel worksheet (each time) after the user clicks a command button to run the following VBA code.

However, I'd like it to create just ONE new Excel worksheet initially and then append later selections onto that same spreadsheet. What changes would I need to make to the code for this to happen?



Private Sub shootoutexcel_Click()
On Error GoTo err_handler
Dim x As Integer, y As Integer
Dim itm As Variant
Dim xlNew As Excel.Application
Dim sht
Set xlNew = GetObject(, "Excel.Application")

If TypeName(xlNew) = "Nothing" Then
Set xlNew = CreateObject("Excel.Application")
End If
'Set xlNew = CreateObject("Excel.application")

With xlNew
.Workbooks.Add
.Sheets.Add
For Each itm In Me.lstCustInfo.ItemsSelected
x = x + 1
For y = 1 To Me.lstCustInfo.ColumnCount - 1
.Sheets(1).Cells(x, y) = Me.lstCustInfo.Column(y, itm)
Next
Next
End With
xlNew.Visible = True
Exit Sub
err_handler:
If Err.Number = 429 Then
Resume Next
Else
MsgBox Err.Number & " " & Err.Description, vbOKOnly, "Error"
End If


End Sub

 

[PHV]

In the VBA Visual Basic for Applications (Microsoft) forum707 FAQ area you'll find code to retrieve the last used row of a worksheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mca2k4]

I think I found the FAQ you're referring to:

http://www.tek-tips.com/faqs.cfm?fid=4001

However, after I input it into my userform code, upon loading the form I immediately get the error message "The expression On Click you entered as the event property setting produced the following error: Duplicate Option statement."


In debug, it highlights my second "Option Explicit" line at the bottom here:


Option Compare Database
Option Explicit

Dim strWhere As String
Const csSql As String = "SELECT * FROM Region6 "
Dim strOrder As String

Option Explicit

 

[PHV]

Simply get rid of the spurious Option line ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mca2k4]

If I go ahead and delete the second "Option Explicit" declaration, it then gives me this error (upon clicking the command button 'BtnAdd'):

"Compile error: Variable not defined"



It then goes on to highlight this line:

Private Sub BtnAdd_click()

 

[mca2k4]

After making all the necessary changes from the FAQ's generic code and adapting it to the Text/List/Combo boxes of my form, I'm still getting this same error of

"Compile error: Variable not defined"


with this line highlighted:

Private Sub BtnAdd_click()


Any suggestions?

 

[mca2k4]

I just made some corrections to the code and now it highlights this line (that comes immediately after the private sub line)

If optionYes = True Then
^^^^^^^^^^^^^^^^

 

[PHV]

Why not posting the code module ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mca2k4]

Here is my "Global Code" module utilizing the code from the FAQ. Now when I click the command button "BtnAdd" on my form Region6, nothing happens.





Option Compare Database

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function

Option Explicit
'The Row variable isn't just a name, not the
'actual row of cells
Dim Data As Object
Dim Row As Integer
Dim I As Integer

Private Sub UserForm_Initialize()
Set Data = Sheets("Sheet1").Cells
End Sub

Private Sub BtnAdd_click()
'Row references that cell in the worksheet. The (1, 27) is
'row 1, column 27, which is where I said to put it.
Row = Data(1, 27) + 1


'Put all your error checking here eg check all the fields
'are entered correctly

'These are just examples, change them to whatever fields you want...
Data(Row, 1) = MSID
Data(Row, 2) = ItemNumber
Data(Row, 3) = Description
Data(Row, 4) = Unit
Data(Row, 5) = Placeholder
Data(Row, 6) = UnitPrice
Data(Row, 7) = BidPriceRange
Data(Row, 8) = DateofLastUpdate
Data(Row, 9) = Comment
Data(Row, 10) = Lookup

If optionYes = True Then
Data(Row, 8) = "Yes"
Else
Data(Row, 8) = "No"
End If

 

[PHV]

1) Option Explicit must be just below Option Compare Database
2) Set Data = Sheets("Sheet1").Cells Sheets must be qualified with an instantiated Excel object, for example:
Set Data = xlNew.Sheets("Sheet1").Cells
3) optionYes should be declared unless it's a control in the same form as BtnAdd.
4) I don't see the End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mca2k4]

I did everything you mentioned except step 3; how do I declare optionYes? (even though it's on the same form as BtnAdd, it's still giving me the optionYes "variable not defined" error)

 

[mca2k4]

I've already got this at the top of my form's code:

Option Compare Database
Option Explicit

 

[PHV]

Either:
If Me!optionYes = True Then
Or :
If Me![name of option group] = ValueIfTrue Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886