PDA

View Full Version : [SOLVED] Can't copy data from new created workbook



arnoldasz
06-24-2018, 10:44 AM
Hello,

I have made an function that creates new workbook (Workbooks.Add) and stores some info from the array to the first sheet of created workbook. Workbook is created with user form button click. I am going to explain my problem through steps:

1. I open any excel file, for example "book1.xlsx"
2. Run my userform and click button
3. Now "newbook.xlsx" is created and opened

But now, until I have not closed userform, I can't copy any info from 'newbook.xlsx' and if I try to close 'newbook.xlsx', I get a message 'Do you really want to close 'book1.xlsx' (wrong workbook name)

If I close userform, everything works fine. Maybe someone have any ideas why it's happening? Function:


Private Function showGlandsResult(arr() As String)
'On Error GoTo HandleErrors

Application.DisplayAlerts = False

Dim arrLength As Long
Dim newWBook As Workbook
Dim i As Long
Dim j As Long
Dim startRow As Long
Dim curRow As Long

arrLength = UBound(arr)
curRow = 2

Set newWBook = Workbooks.Add(xlWBATWorksheet)

'Surasom masyvo reiksmes y nauja knyga
With newWBook.Worksheets(1)
.Name = "Sandarikliai"

.Cells(1, RES_CELL_DESC) = "Kabelis"
.Cells(1, RES_CELL_GLND) = "Sandariklis"
.Cells(1, RES_CELL_MANU) = "Gamintojas"
.Cells(1, RES_CELL_CODE) = "Kodas"
.Cells(1, RES_CELL_QUAN) = "Kiekis"
'reikia ysitikinti, kad RES_CALL_DESC reiksme yra maziausia, o RES_CELL_QUAN - didziausia,
'antraip kodas normaliai neveiks.
With .Range(.Cells(1, RES_CELL_DESC), .Cells(1, RES_CELL_QUAN))
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

For i = 1 To UBound(arr)
'kad zinotume, kurias eilutes sujungti
startRow = curRow

For j = 1 To UBound(arr, 2)
If arr(i, j, RES_CELL_DESC) <> vbNullString Then
.Cells(curRow, RES_CELL_DESC) = arr(i, j, RES_CELL_DESC)
.Cells(curRow, RES_CELL_GLND) = arr(i, j, RES_CELL_GLND)
.Cells(curRow, RES_CELL_MANU) = arr(i, j, RES_CELL_MANU)
.Cells(curRow, RES_CELL_CODE) = arr(i, j, RES_CELL_CODE)
.Cells(curRow, RES_CELL_QUAN) = arr(i, j, RES_CELL_QUAN)

curRow = curRow + 1
End If
Next j

'sujungiam eilutes, kuriose pasikartoja kabelio pavadinimas
.Range(.Cells(startRow, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_DESC)).Merge
Next i

'sulygiuojam ir sutalpinam viska y stulpelius
With .Range(.Cells(2, RES_CELL_GLND), .Cells(curRow - 1, RES_CELL_QUAN))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
.Range(.Cells(1, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_QUAN)).Columns.EntireColumn.AutoFit

End With

newWBook.Activate
HandleErrors:
Application.DisplayAlerts = True
End Function

Hightree
06-24-2018, 10:50 AM
I think you must save It first

arnoldasz
06-24-2018, 11:00 AM
Thank you for fast response, but it didn't solved my problem. But file has been saved without problems.
I have added Workbook.SaveAs line before 'HandleErrors' line

p45cal
06-25-2018, 01:49 AM
I don't think you need to save it first just to copy stuff from it.
I see no line which attempts to make any copy of anything in your code.
Something like:
.Range("A1:E1").Copy ThisWorkbook.Sheets("Sheet1").Range("C10")
directly after:
.Range(.Cells(1, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_QUAN)).Columns.EntireColumn.AutoFit
should copy the range A1:E1 of your new sheet to cell C10:G10 of Sheet1 (as long as there is one of that name) of the workbook that the code is in.
It does here.

Where is this code?

It might be better to make this Function into a Sub, because functions are really to return values, not do things.

arnoldasz
06-25-2018, 11:24 AM
I don't think you need to save it first just to copy stuff from it.
I see no line which attempts to make any copy of anything in your code.
Something like:
.Range("A1:E1").Copy ThisWorkbook.Sheets("Sheet1").Range("C10")
directly after:
.Range(.Cells(1, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_QUAN)).Columns.EntireColumn.AutoFit
should copy the range A1:E1 of your new sheet to cell C10:G10 of Sheet1 (as long as there is one of that name) of the workbook that the code is in.
It does here.

Where is this code?

It might be better to make this Function into a Sub, because functions are really to return values, not do things.

I didn't mention, that I am trying to copy values manualy (CTRL+C), not via VBA. The problem is, all these commands (CTRL+C, CTRL+Z, CTRL+A...) are not working on created sheet, until I close userform. This is strange behavior of excel.

Replacing function with sub doesn't help anyway, but thanks for the advice.

EDIT:

I have found solution. I didn't knew, that excel prevents changes to workbooks, while userform is active. showing UserForm with vbModeless param solves my problem and allows changes to other workbooks:


UserForm.Show(vbModeless)

Thank you for help.