Hi,
I'm trying to use VBA in access to open an excel spreadsheet, replace some content, and copy to another spreadsheet. The repace code works fine in Excel, but if I put it in access, I got "Subscript out of range", Here is the code:
I'm trying to use VBA in access to open an excel spreadsheet, replace some content, and copy to another spreadsheet. The repace code works fine in Excel, but if I put it in access, I got "Subscript out of range", Here is the code:
Code:
Set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
oXL.ScreenUpdating = False
Set objFSOctl = CreateObject("UserAccounts.CommonDialog")
objFSOctl.Filter = "Excel File(*.xls)|*.xls|All Files|*.*"
objFSOctl.FilterIndex = 3
objFSOctl.Flags = &H80000 + &H4 + &H8
InitFSOctl = objFSOctl.ShowOpen
If InitFSOctl Then
openFilename = objFSOctl.fileName
strPosTmp1 = InStrRev(openFilename, "\")
currDirStr = Left(openFilename, strPosTmp1)
openFileStr = Right(openFilename, Len(openFilename) - strPosTmp1)
strPosTmp1 = InStr(openFileStr, ".")
wrkTmpStr = Left(openFileStr, strPosTmp1 - 1)
Set objFSO = CreateObject("SAFRCFileDlg.FileSave")
objFSO.FileType = "Excel file(*.xls)"
objFSO.fileName = currDirStr & wrkTmpStr & "_changed.xls"
InitFSO = objFSO.OpenFileSaveDlg
If InitFSO Then
outfilename = objFSO.fileName
strPosTmp1 = InStrRev(outfilename, "\")
saveDirStr = Left(outfilename, strPosTmp1)
Set srcWorkbook = oXL.Workbooks.Open(openFilename)
If Err.Number = 0 Then
oXL.Workbooks.Add
oXL.ActiveWorkbook.Sheets("Sheet1").Delete
oXL.ActiveWorkbook.Sheets("Sheet2").Delete
Dim i As Integer
i = 1
For Each w In srcWorkbook.Worksheets
w.Copy , oXL.ActiveWorkbook.Worksheets(i)
oXL.ActiveWorkbook.Worksheets("Sheet1").Cells.Replace What:="NULL", _
Replacement:=Null, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False '<== got run time error here, _
no problem if in Excel
i = i + 1
Next w
oXL.ActiveWorkbook.Sheets("Sheet3").Delete
oXL.ActiveWorkbook.SaveAs outfilename
oXL.ActiveWorkbook.Close
End If
srcWorkbook.Close
End If
End If
oXL.ScreenUpdating = True
oXL.DisplayAlerts = True
Set oXL = Nothing