Dim oXL As Object
Dim wkb As Object
Dim wks As Object
Set oXL = CreateObject("excel.application")
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
strFile = Dir(strPath & "\*.xls*")
Do While Len(strFile) > 0
strPathFile = strPath & "\" & strFile
Set wkb = oXL.Workbooks.Open("\\hhhfs02.itsc.hhs-itsc.local\financedata\Policy Data Call Database\Import Files\*.xlsx")
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
wkb.Close True
oXL.Quit
Set oXL = Nothing
strFile = Dir()
Loop
End Sub
Sub LoopFilesAndOpen( _
psPath As String _
, Optional psMask As String = "*.*")
'read files into array and open each one
's4p
'PARAMETERS
' psPath is path to look in
' psMask is what to look for (ie: *.jpg)
Dim psPathFile As String _
, sFilename As String _
, i As Integer
Dim arrFile() As String
psPath = Trim(psPath)
If Right(psPath, 1) <> "\" Then
psPath = psPath & "\"
End If
'first array element will be 0
i = -1
sFilename = Dir(psPath & psMask)
'load files matching mask into an array
Do While sFilename <> ""
If (GetAttr(psPath & "\" & sFilename) And vbDirectory) <> vbDirectory Then
i = i + 1
'redimension array and preserve previous values
ReDim Preserve arrFile(i)
'assign filename to array element
arrFile(i) = sFilename
End If
'get next filename
sFilename = Dir()
Loop
'open all the files
If Not UBound(arrFile) >= 0 Then
'No Files
Exit Sub
End If
'loop through specified files and open
For i = LBound(arrFile) To UBound(arrFile)
psPathFile = psPath & arrFile(i)
Application.FollowHyperlink psPathFile
Next i
End Sub
Corrected that error but now there in line:
Set wkb = oXL.Workbooks.Open("\\hhhfs02.itsc.h hs-itsc.lo cal\financ edata\Poli cy Data Call Database\Import Files\*.xlsx")
run-time error 1004 unable to find file
Option Explicit
to the top of the code in the compiler directives. This will force you to DIMension every variable that is used and also check more when it compiles.
dim oXL as object
and do this: ASKER
Dim oXL As Object
Dim wkb As Object
Dim wks As Object
Set oXL = CreateObject("excel.application")
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
strFile = Dir(strPath & "\*.xls*")
Do While Len(strFile) > 0
strPathFile = strPath & "\" & strFile
Set wkb = oXL.Workbooks.Open(strFile)
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
wkb.Close True
strFile = Dir()
Loop
oXL.Quit
Set oXL = Nothing
ASKER
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
Dim oXL As Object
Dim wkb As Object
Dim wks As Object
Set oXL = CreateObject("excel.application")
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
strFile = Dir(strPath & "\*.xls*")
Do While Len(strFile) > 0
strPathFile = strPath & "\" & strFile
Set wkb = oXL.Workbooks.Open(strPathFile)
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
wkb.Close True
strFile = Dir()
Loop
oXL.Quit
Set oXL = Nothing
ASKER
Set wkb = oXL.Workbooks.Open(strPathFile)
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
wkb.Close True
I'm getting a runtime error 9 subscript out of range in line:
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
Close Request Pending
shieldsco requested that this question be closed on 9/26/2016, as follows:
shieldsco's comment #a41808643 (0 points)
crystal (strive4peace) - Microsoft MVP, Access's comment #a41808042 (500 points)
For the following reason:
Thanks
To cancel this request, state your reason as a comment and click the 'Object' button.
ASKER
ASKER
ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
ASKER
Set wkb = oXL.Workbooks.Open("\\hhhf
run-time error 1004 unable to find file