Solved

VBA delete Excel columns in multiple workbooks from an Access Button

Posted on 2016-09-20
21
38 Views
Last Modified: 2016-10-05
I using the code below to delete columns in Excel Workbooks however I get an error message:


Error
Set wkb = oXL.Workbooks.Open("\\hhhfs02.itsc.hhs-itsc.local\financedata\Policy Data Call Database\Import Files\*.xlsx")


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

Open in new window

0
Comment
Question by:shieldsco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 5
21 Comments
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 250 total points
ID: 41807626
Does the same issue occur if you move these two lines after the Loop statement?

oXL.Quit
Set oXL = Nothing

i.e.

wkb.Close True

 strFile = Dir()
 Loop

oXL.Quit
Set oXL = Nothing

Open in new window

0
 

Author Comment

by:shieldsco
ID: 41807863
Corrected that error but now there in line:
Set wkb = oXL.Workbooks.Open("\\hhhfs02.itsc.hhs-itsc.local\financedata\Policy Data Call Database\Import Files\*.xlsx")


run-time error 1004 unable to find file
0
 
LVL 20
ID: 41807905
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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41808028
Corrected that error but now there in line:
Set wkb = oXL.Workbooks.Open("\\hhhfs02.itsc.hhs-itsc.local\financedata\Policy 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(strPathFile)
0
 
LVL 20
ID: 41808042
you may need to set defaults. File, Options, Trust Center, Trust Center Settings... command button, Trusted Locations:
check --> Allow Trusted Locations on my network
Excel Allow Trusted Locations on my network
is this code running from Access? oXl is set but never dimensioned.  Add:
Option Explicit

Open in new window

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

Open in new window

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)
0
 

Author Comment

by:shieldsco
ID: 41808634
Error in line :
Set wkb = oXL.Workbooks.Open(strFile)

Open in new window



Error
0
 

Author Comment

by:shieldsco
ID: 41808643
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

Open in new window

0
 

Author Comment

by:shieldsco
ID: 41810801
I'm getting a runtime error 9 subscript out of range in line:

wkb.Worksheets("Data Call").Range("AC:HFD").Delete

Open in new window


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

Open in new window

0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41810887
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.
0
 

Author Comment

by:shieldsco
ID: 41810923
Please refer to code in 41810801 :

Set wkb = oXL.Workbooks.Open(strPathFile)
wkb.Worksheets("Data Call").Range("AC:HFD").Delete
wkb.Close True

Open in new window

0
 
LVL 20

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41811062
>subscript out of range in line:

>wkb.Worksheets("Data Call").Range("AC:HFD").Delete

I suspect the workbook being opened does not have a sheet with that name -- or wkb is not actually set.  Earlier I suggested first loading filenames into an array.

Add an error handler to your code.  EE video:

1. basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41811082
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.
0
 

Accepted Solution

by:
shieldsco earned 0 total points
ID: 41811397
Figured it out... no array necessary
0
 
LVL 20
ID: 41811409
glad you got it
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41812247
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.
0
 

Author Comment

by:shieldsco
ID: 41812765
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.
0
 
LVL 20
ID: 41812785
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.
1
 

Author Comment

by:shieldsco
ID: 41813803
My apologies you two should split the points.... 250 each. Administrator, please adjust accordingly. Thanks
0
 

Author Closing Comment

by:shieldsco
ID: 41829548
It was the best solution
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete table fields 3 16
Access #Deleted data 20 43
How do a DCount on a report 1 17
Expand information down with a lot of information 35 38
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question