Solved

How do I change this VBA code to reference the data in a cell instead of the fixed path?

Posted on 2014-07-31
4
205 Views
Last Modified: 2014-08-03
I’m need to change the source of the file from this:
Call UnZip("C:\Users\NC\Desktop\Vishesh\Test", "C:\Users\NC\Desktop\Vishesh\Test\TestZipFile.Zip")

To:
Range("D12") in work sheet "Main Menu", Zip file name: "TestZipFile)


Sub TestRun()
 
    'Change this as per your requirement

    Call UnZip("C:\Users\NC\Desktop\Vishesh\Test", "C:\Users\NC\Desktop\Vishesh\Test\TestZipFile.Zip")
 
End Sub
 
 
 
Sub UnZip(strTargetPath As String, Fname As Variant)
 
    Dim oApp As Object
 
    Dim FileNameFolder As Variant
 
 
 
    If Right(strTargetPath, 1) <> Application.PathSeparator Then
 
        strTargetPath = strTargetPath & Application.PathSeparator
 
    End If
 
 
 
    FileNameFolder = strTargetPath
 
 
 
    Set oApp = CreateObject("Shell.Application")
 
    oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items
 
End Sub

Open in new window

0
Comment
Question by:kbay808
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40233187
If D12 only has the file name then change line 5 to this:
 Call UnZip("C:\Users\NC\Desktop\Vishesh\Test", "C:\Users\NC\Desktop\Vishesh\Test\" & Sheets("Main Menu").Range("D12").Value & ".zip")

Open in new window


You could make this a little easier to handle by assigning the filepath to a variable and using that in the statement like so:
Dim strFilePath as String
strFilePath = "C:\Users\NC\Desktop\Vishesh\Test"
Call UnZip(strFilePath, strFilePath & Sheets("Main Menu").Range("D12").Value & ".zip")

Open in new window


Regards,
-Glenn
0
 

Author Comment

by:kbay808
ID: 40233419
My question must be misleading.  I currently have a button for the user to click that will allow the user to use a windows explorer to navigate to the folder.  That path is then populated into cell D12.  I need to modify the code to accept the path given in D12.  The zip file will always be the same.

Example:
Path= Range (“D12”)
Filename= “TestZipFile.Zip”
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40233438
Okay, here's the modified code for what you described:
Dim strFilePath as String
strFilePath = Sheets("Main Menu").Range("D12").Value
Call UnZip(strFilePath, strFilePath & "TestZipFile.zip")

Open in new window


Hope that helps!
-Glenn
0
 

Author Closing Comment

by:kbay808
ID: 40237069
Thank you very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 31
Auto populate in Cascade dropdown 3 27
change the windows script file to BAT 10 30
Excel Hyperlink Question 4 31
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now