I'm building VBA inside Access to modify an Excel file. I have two fields on my form (txtFileNa and cmbWorksheet) that contain the path&file ("C:\Spreadsheets\Problem.xlsx") and the worksheet name ("ThisSpreadsheet").
I need to specify a worksheet for inserting and populating a new column. The code below works fine as long as I'm putting the column in the first worksheet in the workbook, but if I need to put the column in the 4th worksheet (named "ThisSpreadsheet"), it still goes in the first worksheet. I've tried these changes on line 90:
90 Set xlSheet = xlBook.Worksheets(4)
90 Set xlSheet = xlBook.Worksheets("ThisSpreadsheet")
90 Set xlSheet = xlBook.Worksheets(me.cmbWooksheet)
neither of them work. I think I need to modify lines 100 and 110 to specify the worksheet, but I don't know how. I'd appreciate any suggestions for making the code better, in addition to fixing the specification issue. Thanks!
Dim xl As Excel.Application, xlBook As Excel.Workbook, xlSheet As Excel.Worksheet, filePath As String
60 filePath = Me.txtFileNa
70 Set xl = New Excel.Application
80 Set xlBook = xl.Workbooks.Open(filePath)
90 Set xlSheet = xlBook.Worksheets(1)
'add new column to spreadsheet
'put description in header row:
110 Range("$A$1").Value = "SpreadsheetRowID"
'populate the cells with sequential numbers as long as the rows have data
Dim k, i As Long, n As Long
120 With Range("a2:a" & Range("b" & Rows.Count).End(xlUp).row)
130 k = .Value
140 For i = 1 To UBound(k, 1)
150 If Len(k(i, 1)) = 0 Then
160 n = n + 1
170 k(i, 1) = n
180 End If
200 .Value = k
210 End With
260 Set xl = Nothing
270 Set xlSheet = Nothing
You also need worksheet references in the subsequent code]
Open in new window
PS You should always make sure everything is referenced properly especially when automating Excel from another application.