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 put it a few posts agoWhat is the proper way to close Excel?
or better said, it provides an empty type which behaves as best as it can like a variant.That doesn't do what you are thinking it does. It types IColumn as a Variant.
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?
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
strValue = Right(strValue, Len(strValue)-1)
For x = 2 To lColumn
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