Solved Access VBA To Concatenate Excel Cells (1 Viewer)

[jo15765]

I need to concatenate the rows in my workbook. I want to ignore column A, and only do column B - the value of lColumn

I am capturing lColumn like this

Dim lColumn As Long
lColumn = wb.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Which means I want to take column B - lColumn and Concatenate those values. Now let's say the Excel Data looks like this

Jason 1 3 3 2 4

I want to concatenate the values so they would read 1.3.3.2.4 (essentially add a period between each concatenation) and add that data into the column after lColumn

How would I do this with access vba? if this isn't clear enough please let me know and I'll try to explain further.

 

[Uncle Gizmo]

How would I do this with access vba?

I'm not getting a clear idea of your question. It looks like you have written code for Excel, but you are asking how to do this in MS Access. Could you please clarify this point.

 

[The_Doc_Man]

Can it ever happen that one of the columns would be blank but the next one wouldn't? What do you want to do with the columns from which you obtained data? Eliminate them? Or leave them?

I might just write a loop to step through the columns after the first one, one at a time and if the column isn't blank, tack on "." & column content. Stop the loop at the first time that the column IS blank.

I didn't write code because I'm not clear WHERE you want this result to go.

 

[jo15765]

I am manipulating Excel with Access.

Let me attach a sample workbook that should better illustrate what I'm after. There will never be a blank column, but it could hold a 0.

I'm wanting to after the last column with data, add the concatenated values.

This is a .xlsx but for some reason I couldn't upload that file type so I changed the extension to .txt

 

[jo15765]

Okay - seems changing the extension changed it to pure garbage. Here is a google drive link

test.xlsx

drive.google.com

 

[Uncle Gizmo]

Are you creating a new Excel sheet each time? Are you updating an existing Excel sheet?

 

[jo15765]

Are you creating a new Excel sheet each time? Are you updating an existing Excel sheet?

It's updating an existing workbook. The worksheet comes in like what my sample workbook shows. And columns are concatenated on the same worksheet.

This process will be run twice daily on two different workbooks.

Just trying to automate a process that is currently manual.

 

[Isaac]

You can use Excel vba in access. The optimal solution would use late binding, to avoid reference and version issues across a user base, but it's a bit easier to code using early binding. Coding in early and deploying in late is a common approach. Anyway, you might start out like this:

Dim ExcelApp as Object, wb as object, ws as object, x as long, rng as object, lColumn As Long
set ExcelApp = createobject("Excel.Application")
set wb = ExcelApp.Workbooks.Open("path to workbook")
set ws = wb.sheets("name of sheet")
lColumn = ws.Cells(1, Columns.Count).End(-4159).Column
for x = 1 to IColumn
'....Your concatenation code goes here.  As previous replies have suggested, some of the conditions must be thought out
next x

 

[jo15765]

Yes, my struggle comes in when trying to actually write the concatenation code. I know Excel has a built in Concat() function, but it doesn't have the ability to add a . in between each value.

 

[Isaac]

Dim ExcelApp as Object, wb as object, ws as object, x as long, rng as object, lColumn As Long
set ExcelApp = createobject("Excel.Application")
set wb = ExcelApp.Workbooks.Open("path to workbook")
set ws = wb.sheets("name of sheet")
lColumn = ws.Cells(1, Columns.Count).End(-4159).Column
dim strValue as string
for x = 1 to IColumn
    strValue = strValue & "." & ws.cells(1,x)
    'note - I used row 1 because you didn't specify...
next x
strValue = right(strValue,len(strValue)-1)
ws.cells(1,IColumn).value=strValue

 

[jo15765]

Okay, I've got

For x = 1 to lColumn
    strValue = strValue & "." & wb.Sheets(1).Cells(2,x)
Next x

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

Now the code will hit the last line in the above code block and give me the error

Run-time error '5':

Invalid procedure call or argument

 

[Isaac]

Sorry, I had a typo - change strtValue to strValue. I'm doing all this from memory.

 

[Gasman]

You would need the .Value property from the cell?

 

[jo15765]

Err, that was actually a typo on my part.

Still same error.

Amazing you can do this from memory!

 

[Isaac]

and of course, may want to add some cleanup code like

wb.close True
ExcelApp.displayalerts=false
ExcelApp.quit

 

[Isaac]

can you post 100% of your current code?

 

[Gasman]

Err, that was actually a typo on my part.

Still same error.

Amazing you can do this from memory!

You need to copy and paste the code, not retype it.
On what line does this error occur?, walk through the code with F8 in the debug window.

 

[jo15765]

This is my current code

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

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

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

For x = 1 To lColumn
    strValue = strValue & "." & wb.Sheets(1).Cells(2, x)
Next x

strValue = Right(strValue, Len(strValue) - 1)
wb.Sheets(1).Cells.Value = strValue

wb.Save
wb.Close


Set wb = Nothing
Set xlApp = Nothing

End Function

 

[Isaac]

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?

 

[Gasman]

And you still are not sing the value property?
What is lColumn meant to be?