Solved

How to get new file in VBA Code

Posted on 2011-09-15
2
197 Views
Last Modified: 2012-05-12
I need to alter the 'GetNewFile' code in VBA in the attached spreadsheet so that when the Target Import File button is clicked a new file is imported each time. At the moment it shows the location of the new target file in the button but when 'Import Data' is clicked it still imports the previous file. I have attached the spreadsheet containing the macro and also the two excel files containing the information that I want to import into the spreadsheet.

Thanks in advance.
WLK-Raw-Template.xlsm
Book1.xls
Book1345.xls
0
Comment
Question by:Lozza64
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 36547679
I think I've fixed it - did a few repairs (bugging me from prior question). See changes in ThisWorkbook module, I created a GetNewFileProcess() routine in module run that is called from workbook_open, and the button click (identifying a new file)

A couple constructive inputs.  You declare oApp as new excel.application.  Then, once the file is opened, you set oApp = CreateObject("Excel.Application")

The rest of BOTH is to generate 2 new instances of Excel  I changed your declaration to oApp as Excel.Application (not I'm not using the NEW).  Either you have to do that, or use NEW in your declaration, but don't create the object.  I think the latter is better and easier to port to others, as a good practice for late binding issues, though not a biggie because we are running Excel, right?  :)

Also, there was some awkwardness with selecting a new import file.  I reset the logic such that if you select the exact same path, nothing is done, if its different, then we CLOSE the hidden instance (re: oApp.quit) so we don't get yet ANOTHER instance created....  I had 10 instances up testing the last question, so was glad I had the opportunity to help you out with this one and help clean it up.

Code in ThisWorkbook:
 
Private Sub Workbook_Deactivate()
    SaveAndClose
End Sub

Private Sub Workbook_Open()
Stop
    Sheets("Existing Master Data").CommandButton1.Caption = "Target Origin File: <> ... Missing"
    gstOriginName = GetSetting(APP_CATEGORY, APPNAME, "OriginFile", vbNullString)
    
    'Case first Time Prompt for File Location
    If gstOriginName = vbNullString Then
        GetNewFileProcess
    Else
        Application.EnableEvents = False
        
        If Not oApp Is Nothing Then oApp.Quit
        Set wbOrigin = Nothing
        Set wsOrigin = Nothing
        Set oApp = Nothing
        
        oldOriginFile = gstOriginName 'for future test to see if existing wb already specified
        Set oApp = CreateObject("Excel.Application")
        Set wbOrigin = oApp.Workbooks.Open(gstOriginName)
        Set wsOrigin = wbOrigin.Worksheets("Sheet1")
        
        'Update CommandButton1 caption to show Actual File in use
        Sheets("Existing Master Data").CommandButton1.Caption = "Target Import File: <" & gstOriginName & "> ... Activated"
        
        Application.EnableEvents = True
    End If

End Sub

Open in new window


Code in Module1 that is relevant to this post:
 
Public Const APP_CATEGORY = "Software JG"
Public Const APPNAME = "OriginFile"

Global oApp As Excel.Application 'NEW is not needed, as you use CreateObject to instantiate the oApp object
Global wbOrigin As Workbook
Global wsOrigin As Worksheet
Global wbMain As Workbook

Global gstOriginName As String

Sub SaveAndClose()
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False


If Not wbOrigin Is Nothing Then wbOrigin.Close savechanges:=False

SaveSetting APP_CATEGORY, APPNAME, "OriginFile", gstOriginName

Set wbOrigin = Nothing
Set wsOrigin = Nothing

If Not oApp Is Nothing Then oApp.Quit
Set oApp = Nothing


Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub GetNewFileProcess()
Dim oldOriginFile As String
    
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Call GetNewFile
    
    If oldOriginFile <> gstOriginName Then 'delete the existing hidden instance and open new file, if different - note this checks full path name
        If Not oApp Is Nothing Then oApp.Quit 'get rid of hidden instance, before opening a new file
        
        'Update CommandButton1 caption to show Actual File in use
        Sheets("Existing Master Data").CommandButton1.Caption = "Target Import File: <" & gstOriginName & "> ... Activated"
    
        If Not oApp Is Nothing Then oApp.Quit
        Set wbOrigin = Nothing
        Set wsOrigin = Nothing
        Set oApp = Nothing
        
        Set oApp = CreateObject("Excel.Application")
        Set wbOrigin = oApp.Workbooks.Open(gstOriginName)
        Set wsOrigin = wbOrigin.Worksheets("Sheet1")
        
        oldOriginFile = gstOriginName 'stores full path of file
    End If

    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
End Sub
Sub GetNewFile()

gstOriginName = GFileName("*.xls*")

If gstOriginName <> "" And Dir(gstOriginName) <> "" Then
    
    'Update CommandButton1 caption to show Actual File in use
    Sheets("Existing Master Data").CommandButton1.Caption = "Target Import File: <" & gstOriginName & "> ... Activated"
    
Else
    MsgBox ("No file has been selected or the file does not exist, therefore data cannot be Exported" _
        & " until valid file has been selected." & Chr(10) & Chr(10) _
        & "Please press on the command bar to choose a file.")

End If
End Sub

Open in new window


See attached.

Enjoy!

Dave
WLK-Raw-Template-r1b.xlsm
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 36556756
Hi Lozza64,

Sorry I had done it previously but didn't pay attention that if the file is changed while the workbook still open it would still keep the old location. My mistake.

Pls do the following:
Simply copy the following 3 lines
 
Set oApp = CreateObject("Excel.Application")
Set wbOrigin = oApp.Workbooks.Open(gstOriginName)
Set wsOrigin = wbOrigin.Worksheets("Sheet1")

Open in new window


and put them in the Sub GetNewFile after then instruction:
Sheets("Existing Master Data").CommandButton1.Caption = "Target Import File: <" & gstOriginName & "> ... Activated"

so that the whole Sub GetNewFile will look like this

 
Sub GetNewFile()

gstOriginName = GFileName("*.xls*")

If gstOriginName <> "" And Dir(gstOriginName) <> "" Then
    
    'Update CommandButton1 caption to show Actual File in use
    Sheets("Existing Master Data").CommandButton1.Caption = "Target Import File: <" & gstOriginName & "> ... Activated"
    Set oApp = CreateObject("Excel.Application")
    Set wbOrigin = oApp.Workbooks.Open(gstOriginName)
    Set wsOrigin = wbOrigin.Worksheets("Sheet1")
Else
    MsgBox ("No file has been selected or the file does not exist, therefore data cannot be Exported" _
        & " until valid file has been selected." & Chr(10) & Chr(10) _
        & "Please press on the command bar to choose a file.")
    
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    
    Exit Sub
End If
End Sub

Open in new window


Last but not least you need to move the End if in Sub Workbook_open to be under the 3 above instructions. So that the final Sub Workbook_Open looks like this:

 
Private Sub Workbook_Open()
Sheets("Existing Master Data").CommandButton1.Caption = "Target Origin File: <> ... Missing"
gstOriginName = GetSetting(APP_CATEGORY, APPNAME, "OriginFile", vbNullString)

'Case first Time Prompt for File Location
If gstOriginName = vbNullString Then
    GetNewFile
Else
    'Update CommandButton1 caption to show Actual File in use
    Sheets("Existing Master Data").CommandButton1.Caption = "Target Import File: <" & gstOriginName & "> ... Activated"
    Set oApp = CreateObject("Excel.Application")
    Set wbOrigin = oApp.Workbooks.Open(gstOriginName)
    Set wsOrigin = wbOrigin.Worksheets("Sheet1")
End If

End Sub

Open in new window


Sorry for this oversight. I have attached your whole macro worksheet with the above modifications but the data contains some test data. Sorry again
gowflow
WLK-Raw-Template.xlsm
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

24 Experts available now in Live!

Get 1:1 Help Now