zhshqzyc
asked on
Excel VBA wrong code
Please look at my code.
Option Explicit
Private Sub CommandButton1_Click()
Dim FullFileName As String
FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
1, "Custom Dialog Title", , False)
Workbooks.Open FullFileName
Dim wbOpen As Workbook
Set wbOpen = Workbooks.Open(FullFileName)
Dim xSheet As Worksheet
xSheet = wbOpen.Worksheet 'wrong here
Dim m As Integer
Dim n As Integer
m = xlCellTypeLastRow
n = xlCellTypeLastCol
With wbOpen
Dim X As Long
Dim Y As Long
X = xlCellTypeLastRow
Y = xlCellTypeLastCol
Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer
For i = 1 To X
Sheet2.Cells(i, 1) = xSheet(1).Cells(i, 1)
Sheet2.Cells(i, 2) = xSheet(1).Cells(i, 2)
Sheet2.Cells(i, 3) = xSheet(1).Cells(i, 3)
For a = 1 To m
If (Sheet1.Cells(a, 3) = xSheet.Cells(i, 1) And Sheet1.Cells(a, 4) = xSheet.Cells(i, 2) And Sheet1.Cells(a, 5) = xSheet.Cells(i, 3)) Then
b = a
Exit For
End If
Next a
Sheet2.Cells(i, 4) = Sheet1.Cells(a, 1)
Sheet2.Cells(i, 5) = Sheet1.Cells(a, 2)
Next i
End With
End Sub
Function xlCellTypeLastRow()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
xlCellTypeLastRow = LastRow
End Function
Function xlCellTypeLastCol()
Dim LastCol As Long
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
xlCellTypeLastCol = LastCol
End Function
ASKER
Still wrong. The object doesn't support this property of method.
dim xSheet as worksheet
set xSheet =wbopen.worksheet.add ' if you're trying to add a new worksheet...
set xSheet =wbopen.worksheet.add ' if you're trying to add a new worksheet...
ASKER
I have several worksheets. How to add them?
Take a couple minutes and articulate what you're trying to do. The current state of your original question is "what is wrong with my code" so we focused on your syntax error. Now, you want more help, and I'm glad to do that if you would highlight a few steps that indicate what you're trying to do with the code at a level which I can quickly respond.
Looking forward to your response,
Dave
Looking forward to your response,
Dave
The short answer to your question with no additional consideration is something like:
wbOpen.Worksheet.Add count:=3 ' to add 3 sheets
Dave
wbOpen.Worksheet.Add count:=3 ' to add 3 sheets
Dave
Here's one more example you might find useful to adopt to your code, if you're just trying to add a number of worksheets....
Sub AddSheets()
Dim mySheets() As Worksheet
Dim numSheetsAdded As Integer
numSheetsAdded = 3
For i = 0 To numSheetsAdded
ReDim Preserve mySheets(i)
Set mySheets(i) = Worksheets.Add
Next i
End Sub
Sub AddSheets()
Dim mySheets() As Worksheet
Dim numSheetsAdded As Integer
numSheetsAdded = 3
For i = 0 To numSheetsAdded
ReDim Preserve mySheets(i)
Set mySheets(i) = Worksheets.Add
Next i
End Sub
ASKER
Hi dlmille:
I just steped into the code,I could not go further. I have several sheets. I want to extract data to one sheet and write to the other sheet by some condition.
I just steped into the code,I could not go further. I have several sheets. I want to extract data to one sheet and write to the other sheet by some condition.
Option Explicit
Private Sub CommandButton1_Click()
Dim FullFileName As String
FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
1, "Custom Dialog Title", , False)
Workbooks.Open FullFileName
Dim wbOpen As Workbook
Set wbOpen = Workbooks.Open(FullFileName)
Dim xSheet1 As Worksheet
Dim xSheet2 As Worksheet
xSheet1 = wbOpen.Worksheets.Add
Dim m As Integer
Dim n As Integer
m = xlCellTypeLastRow
n = xlCellTypeLastCol
' With wbOpen
Dim X As Long
Dim Y As Long
X = xlCellTypeLastRow
Y = xlCellTypeLastCol
Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer
For i = 1 To X
xSheet1(2).Cells(i, 1) = xSheet1(1).Cells(i, 1)
xSheet1(2).Cells(i, 2) = xSheet1(1).Cells(i, 2)
xSheet1(2).Cells(i, 3) = xSheet1(1).Cells(i, 3)
For a = 1 To m
If (Sheet1.Cells(a, 3) = xSheet1(1).Cells(i, 1) And Sheet1.Cells(a, 4) = xSheet1(1).Cells(i, 2) And Sheet1.Cells(a, 5) = xSheet1(1).Cells(i, 3)) Then
b = a
Exit For
End If
Next a
xSheet(1).Cells(i, 4) = Sheet1.Cells(a, 1)
xSheet(1).Cells(i, 5) = Sheet1.Cells(a, 2)
Next i
' End With
End Sub
Function xlCellTypeLastRow()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
xlCellTypeLastRow = LastRow
End Function
Function xlCellTypeLastCol()
Dim LastCol As Long
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
xlCellTypeLastCol = LastCol
End Function
So, you're opening a file that has several sheets. You want to extract data from those sheets into a new sheet that is added at the beginning?
ASKER
No, suppose I open a file by FileOpen dialog
FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
1, "Custom Dialog Title", , False)
Then in this excel file there are already three sheets. Sheet1 has data, I want to grab dat from sheet1 and write them to sheet2.
The Excel file has 3 sheets, let's say their names are Sheet1, Sheet2, and Sheet3 - and all three exist. Is there data in all three sheets, or just in Sheet1. Do you really need to add a sheet (e.g., create Sheet4), or just use the blank Sheet2 (is Sheet2 blank)?
Dave
Dave
ASKER
No, I don't want to add new sheet. Now I just want to use a blank sheet 2. I might misunderstand the add method.
ok - give me a second - I'm rewriting your code to patch things up...
Dave
Dave
Ok - I've not tried to follow the logic as much as try to identify the things you want to identify.
To get a handle to a sheet, there are several ways:
dim mySheet as worksheet
set mysheet = worksheets.add (this creates a new sheet and gives the handle to that sheet as mySheet)
another way...
activeworkbook.sheets(1) <- this is a reference to the Sheet1 sheet using the index of 1
activeworkbook.sheets(2) <- same thing with index of 2, for Sheet2
finally another way...
activeworkbook.sheets("she etname") <- referencing the sheet using a sheet name
you could assign mySheet to any of these using the
set mySheet = activeworkbook.sheets(1)
or in your example...
set mySheet = myOpen.Sheets(1)
I've attempted to replace your code and you can correct as you understand your logic. Sheet(1) is the first sheet (default "Sheet1"), and Sheet(2) is the second (default "Sheet2")
See code below:
Does this help?
dave
To get a handle to a sheet, there are several ways:
dim mySheet as worksheet
set mysheet = worksheets.add (this creates a new sheet and gives the handle to that sheet as mySheet)
another way...
activeworkbook.sheets(1) <- this is a reference to the Sheet1 sheet using the index of 1
activeworkbook.sheets(2) <- same thing with index of 2, for Sheet2
finally another way...
activeworkbook.sheets("she
you could assign mySheet to any of these using the
set mySheet = activeworkbook.sheets(1)
or in your example...
set mySheet = myOpen.Sheets(1)
I've attempted to replace your code and you can correct as you understand your logic. Sheet(1) is the first sheet (default "Sheet1"), and Sheet(2) is the second (default "Sheet2")
See code below:
Option Explicit
Private Sub CommandButton1_Click()
Dim FullFileName As String
Dim wbOpen As Workbook
Dim xSheet1 As Worksheet
Dim xSheet2 As Worksheet
Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer
Dim X As Long
Dim Y As Long
Dim m As Integer
Dim n As Integer
'Open a worksheet file based on user input
FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title", , False)
Workbooks.Open FullFileName
Set wbOpen = Workbooks.Open(FullFileName) 'workbook has 3 sheets
m = xlCellTypeLastRow
n = xlCellTypeLastCol
X = xlCellTypeLastRow
Y = xlCellTypeLastCol
For i = 1 To X
wbOpen.Sheets(2).Cells(i, 1) = wbOpen.Sheets(1).Cells(i, 1)
wbOpen.Sheets(2).Cells(i, 2) = wbOpen.Sheets(1).Cells(i, 2)
wbOpen.Sheets(2).Cells(i, 3) = wbOpen.Sheets(1).Cells(i, 3)
For a = 1 To m
If (wbOpen.Sheets(1).Cells(a, 3) = wbOpen.Sheets(2).Cells(i, 1) And wbOpen.Sheets(1).Cells(a, 4) = wbOpen.Sheets(2).Cells(i, 2) And wbOpen.Sheets(1).Cells(a, 5) = wbOpen.Sheets(2).Cells(i, 3)) Then
b = a
Exit For
End If
Next a
wbOpen.Sheets(1).Cells(i, 4) = wbOpen.Sheets(2).Cells(a, 1)
wbOpen.Sheets(1).Cells(i, 5) = wbOpen.Sheets(2).Cells(a, 2)
Next i
' End With
End Sub
Function xlCellTypeLastRow()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
xlCellTypeLastRow = LastRow
End Function
Function xlCellTypeLastCol()
Dim LastCol As Long
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
xlCellTypeLastCol = LastCol
End Function
Does this help?
dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The code is running perfectly. Let me just check the logic. Thank you very much.
change
xSheet = wbOpen.Worksheet(1)