RguyK128
06-26-2008, 06:17 PM
Please bear with me as I'm really stuck and I need help very bad. I get an error running my Excel VBA code which is "Wrong Number of Arguments or Invalid Property Assignment". I have a sub (do6MM) that is called out in another sub (cmdbutton_Click) because I needed to split them as my code was too long.
Basically my code is:
Private Sub cmdbutton_Click()
Dim colcounter As Integer
Dim thickcounter As Integer
Dim xlworkbookpath As String
Dim xlActiveWkbk As Excel.Workbook
Dim xlApp As Excel.Application
Dim xlsheet As Excel.Worksheet
xlworkbookpath = "C:\Documents and Settings\admin\Desktop\book1.xls"
Set xlActiveWkbk = GetObject(xlworkbookpath)
Set xlApp = xlActiveWkbk.Parent
Set xlsheet = xlActiveWkbk.Sheets("SHEET1")
'...Misc Code
If size.Text = "4MM" Then
thickcounter = colcounter + 1
xlsheet.Range(xlsheet.Columns(colcounter), xlsheet.Columns(thickcounter)).Copy
'copy new column of thickness data from spreadsheet A
Windows("book2.xls").Activate
Worksheets("Sheet2").Select
Cells(1, colcounter).Select
ActiveSheet.Paste
'...Misc Code
Else If size.Text = "6MM" Then
Call do6MM
End If
End Sub
Sub do6MM()
thickcounter = colcounter + 2
xlsheet.Range(xlsheet.Columns(colcounter), xlsheet.Columns(thickcounter)).Copy
'copy new column of thickness data from spreadsheet A
Windows("book2.xls").Activate
Worksheets("Sheet2").Select
Cells(1, colcounter).Select
ActiveSheet.Paste
'...Misc Code
End Sub
So basically, if the user selects size "4MM", the program copies certain columns and transfers it to another workbook. The reason I needed to GetObject() is because the two workbooks are forced to run on separate instances without my control.
The 4MM code works fine, but then when I call out the do6MM sub, the problem I am having is when "xlsheet.Range(xlsheet.Columns(colcounter), xlsheet.Columns(thickcounter)).Copy" where it is highlighted and I am getting the error code.
I'm pretty sure I am not referencing things correctly as this GetObject() command has been so frustrating for me. Even when I still reference these codes:
xlworkbookpath = "C:\Documents and Settings\admin\Desktop\book1.xls"
Set xlActiveWkbk = GetObject(xlworkbookpath)
Set xlApp = xlActiveWkbk.Parent
Set xlsheet = xlActiveWkbk.Sheets("SHEET1")
in the do6MM sub, I still get the error. What am I doing wrong with the xlsheet.Range command?
Thank you to anyone who can help!
-Robert
Basically my code is:
Private Sub cmdbutton_Click()
Dim colcounter As Integer
Dim thickcounter As Integer
Dim xlworkbookpath As String
Dim xlActiveWkbk As Excel.Workbook
Dim xlApp As Excel.Application
Dim xlsheet As Excel.Worksheet
xlworkbookpath = "C:\Documents and Settings\admin\Desktop\book1.xls"
Set xlActiveWkbk = GetObject(xlworkbookpath)
Set xlApp = xlActiveWkbk.Parent
Set xlsheet = xlActiveWkbk.Sheets("SHEET1")
'...Misc Code
If size.Text = "4MM" Then
thickcounter = colcounter + 1
xlsheet.Range(xlsheet.Columns(colcounter), xlsheet.Columns(thickcounter)).Copy
'copy new column of thickness data from spreadsheet A
Windows("book2.xls").Activate
Worksheets("Sheet2").Select
Cells(1, colcounter).Select
ActiveSheet.Paste
'...Misc Code
Else If size.Text = "6MM" Then
Call do6MM
End If
End Sub
Sub do6MM()
thickcounter = colcounter + 2
xlsheet.Range(xlsheet.Columns(colcounter), xlsheet.Columns(thickcounter)).Copy
'copy new column of thickness data from spreadsheet A
Windows("book2.xls").Activate
Worksheets("Sheet2").Select
Cells(1, colcounter).Select
ActiveSheet.Paste
'...Misc Code
End Sub
So basically, if the user selects size "4MM", the program copies certain columns and transfers it to another workbook. The reason I needed to GetObject() is because the two workbooks are forced to run on separate instances without my control.
The 4MM code works fine, but then when I call out the do6MM sub, the problem I am having is when "xlsheet.Range(xlsheet.Columns(colcounter), xlsheet.Columns(thickcounter)).Copy" where it is highlighted and I am getting the error code.
I'm pretty sure I am not referencing things correctly as this GetObject() command has been so frustrating for me. Even when I still reference these codes:
xlworkbookpath = "C:\Documents and Settings\admin\Desktop\book1.xls"
Set xlActiveWkbk = GetObject(xlworkbookpath)
Set xlApp = xlActiveWkbk.Parent
Set xlsheet = xlActiveWkbk.Sheets("SHEET1")
in the do6MM sub, I still get the error. What am I doing wrong with the xlsheet.Range command?
Thank you to anyone who can help!
-Robert