0

i need help , i want to insert the value of the excel cell into an array(String array) how to do that ?

my code:

Dim DB_Columns(5) As String 'Columns values Array
Dim i As Integer
Dim tmp_col As String
For i = 0 To 5
    tmp_col = worksheet1.Cells(0, i).value
    DB_Columns(i) = tmp_col
    i = i + 1
Next

every time i run the code i got an exception.

Gedaan
  • 31
  • 1
  • 7

2 Answers2

1

Not sure it's going to fix your problem as you haven't actually told us what the exception is - but you increment i manually in your code even though it's already in a For loop. This means you're going to exit the loop early anyway.

Also - Cells(0, i) will cause an exception because the Cells collection isn't zero-indexed. It expects a row and column index (there isn't a row/column "0").

Finally, you haven't shown us where you assign worksheet1 - unless you have a reference set to the workbook you will probably need to explicitly qualify it (this is a good habit to get into anyway)

FWIW this is how I would write that code:

Dim worksheet1 As Excel.Worksheet = MyXLApp.Workbooks("My Workbook.xlsx").Sheets(1) '// For Example
'// or alternatively something like
'// Dim worksheet1 As Excel.Worksheet = MyXLApp.ActiveWorkbook.Sheets(1)

Dim DB_Columns(0 To 5) As String

For i = LBound(DB_Columns) To UBound(DB_Columns)
    DB_Columns(i) = worksheet1.Cells(1, i + 1).value '// Notice I've added 1 to "i" to prevent an exception as rows/columns start from 1
Next

You can actually do this without a loop also:

Dim DB_Columns() As String

DB_Columns = worksheet1.Cells(1, 1).Resize(1, 5).Value
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • thanks alot , thats worked for me , its even solved another problems lol :P . – Gedaan Dec 28 '16 at 16:10
  • no worries - you should still consider adding the text from the exception to your original question as it may help others in the future that have the same problem(s) – SierraOscar Dec 28 '16 at 17:08
-1

Try this:

tmp_col = worksheet1.Cells.Cells(0, i).value

it works for me.

have a nice day.

chrs04
  • 41
  • 7