shieldsco
asked on
VBA delete Excel columns in multiple workbooks from an Access Button
I using the code below to delete columns in Excel Workbooks however I get an error message:
Set wkb = oXL.Workbooks.Open("\\hhhf s02.itsc.h hs-itsc.lo cal\financ edata\Poli cy Data Call Database\Import Files\*.xlsx")
Set wkb = oXL.Workbooks.Open("\\hhhf
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
instead of looking for the file and doing something with it in the same loop, load file names into an array and then process them. This code is designed to loop through files and then open them -- but instead of opening, you can do something else
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
OK, guessing what your code is supposed to do, please replace that line with this one:
Set wkb = oXL.Workbooks.Open(strPath
you may need to set defaults. File, Options, Trust Center, Trust Center Settings... command button, Trusted Locations:
check --> Allow Trusted Locations on my network
is this code running from Access? oXl is set but never dimensioned. Add:
oXl.quit
and
Set oXL = Nothing
AFTER the loop, not in it. Also, before that, release the worksheet object then the workbook object (first save then close and release)
check --> Allow Trusted Locations on my network
is this code running from Access? oXl is set but never dimensioned. Add:
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: oXl.quit
and
Set oXL = Nothing
AFTER the loop, not in it. Also, before that, release the worksheet object then the workbook object (first save then close and release)
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
I'm getting a runtime error 9 subscript out of range in line:
Here is the full code:
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
Here is the full code:
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
This thread is going off at a tangent!
shieldsco: In response to ID: 41808634 please re-read, then correct your code to match what I provided in ID: 41808028.
shieldsco: In response to ID: 41808634 please re-read, then correct your code to match what I provided in ID: 41808028.
ASKER
Please refer to code in 41810801 :
Set wkb = oXL.Workbooks.Open(strPathFile)
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
wkb.Close True
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm getting a runtime error 9 subscript out of range in line:
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
The worksheet "Data Call" does not exist in the filename currently set within the wkb workbook object.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
glad you got it
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.
Yes, I object.
I offered continued assistance (without the need to rewrite the code using an array-based approach, as the question asker stated was not required) but the eventual points were allocated to a comment that does not seem to address the (initial) question.
ASKER
What's the objection .... you received 500 points... you tried pushing me towards what you knew not what I wanted. My solution works great for me with some help from you.
actually, I am the one who suggested an array, what you did not want to do. I also brought up defaults since you were working with a network file, and pointed out errors such as moving code to quit and release out of the loop. [ fanpages ] also pointed out errors, along with what to do to make them right, and should get points too. We are all here to help and freely give you our time. While it is great you figured it out on your own, our ideas helped you get there. Hopefully, being there for you also helped alleviate some of your frustration. It is always frustrating when things don't work! ... and nice that others are there. Awarding points, and saying thank you, is your way of appreciating the time we spend to help you as best we can. Points should be split between all those who gave you helpful advice.
ASKER
My apologies you two should split the points.... 250 each. Administrator, please adjust accordingly. Thanks
ASKER
It was the best solution
ASKER
Set wkb = oXL.Workbooks.Open("\\hhhf
run-time error 1004 unable to find file