Solved Access VBA To Concatenate Excel Cells

[jo15765]

well the problem is maybe this line, which isn't what I provided:

wb.Sheets(1).Cells.Value = strValue

what are you actually trying to do - set the value of every single cell in the workbook (billions of cells) to that value?

I want to concatenate the values of B- F in column G with a period between them. But I don't want to hardcode B:F because those columns can be dynamic

What is the proper way to close Excel?

 

[Isaac]

Right but on which rows

 

[jo15765]

All rows with data. (sans the first row since it will always be the header row)

 

[Isaac]

Oh, you hadn't mentioned this! Then you are going to need more code (an Outer loop) within which to nest your inner loop.
Which column always has data - is A reliable?

 

[Isaac]

What is the proper way to close Excel?

I put it a few posts ago

 

[Isaac]

That doesn't do what you are thinking it does. It types IColumn as a Variant.

or better said, it provides an empty type which behaves as best as it can like a variant.

 

[jo15765]

Oh, you hadn't mentioned this! Then you are going to need more code (an Outer loop) within which to nest your inner loop.
Which column always has data - is A reliable?

Yes column A will always hold the name. Then columns B - ? will hold the counts that I need to concatenate (it varies form workbook to workbook)

 

[Isaac]

Ok, you took my code and changed stuff and dropped stuff to where lColumn was missing its assignment value.
Here is my new code. If you take this and drop stuff and change stuff and then post it back, this back and forth will not work.
This is aircode, if you see an obvious typo you may need to change it.

Function Test()
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application
Dim lColumn as Long, x As Long
Dim strValue As String
Dim rng As Object, ws as object, y as long

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set wb = xlApp.Workbooks.Open("C:\Test\Test.xlsx", False, False)
set ws = wb.sheets(1)
'wb.Sheets(1).Select '''''don't ever use Select or Activate in Excel VBA
dim lastrow as long, lColumn as long
lastrow = ws.range("A" & ws.rows.count).end(-4162).row
lColumn = ws.range("A" & ws.columns.count).end(-4159).column
for y = 2 to lastrow 'start on row2
    strValue=""
   for x = 2-lColumn 'start at col B
           strValue = strValue & "." & ws.Cells(y, x)
   next x
    strValue = Right(strValue, Len(strValue) - 1)
    ws.cells(y,lColumn+1).value=strValue
next y

wb.Save
wb.Close
xlApp.displayalerts=false
xlApp.Quit

End Function

 

[jo15765]

I'm getting an error on the line

For x = 2 -lColumn 'start at col B

it says Expected: To

 

[Isaac]

Sorry, make it

For x = 2 to lColumn 'start at col B

 

[jo15765]

stepping through the code

lastrow = 1728
lColumn = 1

then it hits the line

    strValue = Right(strValue, Len(strValue)-1)

And gives the error
Run-time error '5':
Invalid procedure call or argument

It hits the line

For x = 2 To lColumn

And immediately jumps to the line above in the first code block and gives the error

 

[Isaac]

While it is stopped and yellow highlighting that line, click View>Immediate Window (if not already shown), and ask the immediate window some relevant debugging questions, like:
?StrValue [then hit Enter]
?lColumn [then hit Enter]

let me know what it says ...

 

[jo15765]

Attached is what it shows

 

[Isaac]

Interesting. So the problem is that lColumn isn't working as we'd expect. Are you sure that there are values (headers) in row 1 ?? Can you post your current code?

 

[Isaac]

Aww crap. I made a mistake, one second, I'll post a corrected line

 

[Isaac]

Change

lColumn = ws.range("A" & ws.columns.count).end(-4159).column

to

lColumn = ws.range("XFD1").end(-4159).column

 

[jo15765]

Yes row 1 has headers. Only change I made was I removed a duplicate variable declaration from your code, and changed the For line so it was valid syntax

Function Test()
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application
Dim x As Long
Dim strValue As String
Dim rng As Object, ws As Object, y As Long
Dim lastrow As Long, lColumn As Long

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set wb = xlApp.Workbooks.Open("C:\Test\Test.xlsx", False, False)
Set ws = wb.Sheets(1)


lastrow = ws.Range("A" & ws.Rows.Count).End(-4162).Row
lColumn = ws.Range("A" & ws.Columns.Count).End(-4159).Column
For y = 2 To lastrow
    strValue = ""
   For x = 2 To lColumn
        strValue = strValue & "." & ws.Cells(y, x)
   Next x
    strValue = Right(strValue, Len(strValue) - 1)
    ws.Cells(y, lColumn + 1).Value = strValue
Next y

wb.Save
wb.Close
xlApp.DisplayAlerts = False
xlApp.Quit

End Function

 

[Isaac]

ok thanks, see my previous post about the change

 

[jo15765]

Change

lColumn = ws.range("A" & ws.columns.count).end(-4159).column

to

lColumn = ws.range("XFD1").end(-4159).column

That seems to be giving valid values now...
last row = 1728
lColumn = 7

 

[Isaac]

Yay! Are there any errors?