Macro to Select file location for lookups

[Cat129]

Morning All,

I'm not sure if this is possible but was hoping you could tell me.

I need to write a macro that does lots of lookups and others things by comparing two spreadsheets. The spreadsheet that is being used as the lookup will have a different file name each time it is run. It is renamed to the date it was last updated and then replaced by the one doing the looking up once all the lookups etc have been completed.

I was thinking of creating some sort of pop up that would ask you to select the file and then the file that you select is used through out the rest of the code.

Is this possible? Or am I over complicating things?

Help!

Thanks,
Cat

 

[Cat129]

Anyone out there able to help?

 

[Norie]

Cat

You mentioned code, could you post it and indicate where you would want to use the selected file in it?

 

[Cat129]

Cat

You mentioned code, could you post it and indicate where you would want to use the selected file in it?

Hey, I cant really. Its 14 modules of code....

I'll try to explain a little more

We have a few hundred text files that generated by our CAD system, and the excel workbook in question loops through them all, delimits them, makes the data usable, renames each sheet, and then saves the workbook as todays date. This workbook is then used by various people and data added to it.

In a few days time an update will be done so the macro will be run again, the issue is each time it is run it starts with a blank workbook.

So we have lookups in place, and other code to compare the data between the two, fill in the gaps and generate data based on the lookups, this is all done manually after the data is generated by the first macro.

What I want to do it make this whole thing into one process.

In my head it would be as simple as

1) Pull all our CAD data into excel and let it do its normal thing
2) Select the file location of the previous version to do lookups
3) Run a macro to do all the lookups and generate data

 

[Norie]

Will you need to select the file location for all 14 modules?

 

[Cat129]

Hi,

No it would be for the 3rd item in the list. The macros for this haven't been written yet. But I cant hardcore the file name into it as it will have changed each time its ran

 

[Norie]

Can you post the part of the code where the file location would be required?

 

[Cat129]

It hasn't been created yet. It would be the very first part of the code.

I want a popup that asks the user to select the file.

Then within all the formulas that would need to use it, reference it within the formula

 

[Cat129]

My problem is I dont know how to create the pop up that would allow user to browse to a file, and then return that file location and name into the code

 

[Norie]

You can use Application.GetOpenFilename to allow the user to browse for and select a file.

Dim strBasePath As String
Dim strFullPath As String
Dim strFileName As String
Dim strFilePath As String

    strBasePath = "C:\Test\"
    
    ChDir strBasePath
    
    strFullPath = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", 1, "Choose file", "Select", False)
    
    If strFullPath <> "False" Then
    
        strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
        
        strFilePath = Replace(strFullPath, strFileName, "")
        
        MsgBox "File selected:" & vbTab & strFileName, vbExclamation, "Congatulations!"
        
    End If