VBA Excel Macro To Update List Via External File

Question

I have an excel sheet that has two sheets. One sheet is hidden that has a list of values called "Location." On the main sheet I have created a drop down menu that pulls from the hidden sheet.

How can I store these values in an external file (Excel, .txt, etc.) so I can hit a macro button (VBA) that will replace/update the list on the hidden sheet with any/all new Location values that will be stored in an external file?

Answer 1

I believe this is what you are looking for:

Dim intPointer as Integer
Dim strFileToImport as String
Dim strLine as String

intPointer = FreeFile()
Open strFileToImport For Input Access Read Lock Read As #intPointer
Do Until EOF(intPointer)
    Line Input #intPointer, strLine
    SheetWithLocations.Cells(lngRow, 1).Value2 = strLine
    lngRow = lngRow + 1
Loop

It opens an external txt file called strFileToImport and reads row by row from the txt file and writes it into the SheetWithLocations.

Answer 2

Let it assume it that the filepath of the external file that contains locations is: "D:\Location.xls"

Location.xls has only one Sheet named as 'sheet1' which has the following structure:

Locations
E:\123.txt 
C:\MyFolder\MyOtherFile.xls 
D:\MyFile.xls 
.
.
etc.

and the filepath of the Working Excel File having two WorkSheets (as you have discussed above) is 'D:\MyWokingFileName.xls'

As you said the 'MyWokingFileName.xls' has two sheets, let it assume that the sheets are 'sheet1' and 'sheet2' and sheet2 is hidden.

Now you want a MacroButton on sheet1 to Update Values in sheet2 of the MyWokingFileName.xls.

So the Code for the Macro would be:

Private Sub macroUpdateLocations ()
    Dim myCon As New ADODB.Connection
    Dim myRs As New ADODB.Recordset
    Dim iCounter As Long
    myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Location.xls;Extended Properties=" & """Excel 8.0;HDR=Yes;IMEX=1;""" & ";"
    Set myRs = myCon.Execute("SELECT * FROM `Sheet1$`")
    Worksheets("sheet2").Range("A:A").ClearContents
    Worksheets("sheet2").Range("A1").Value = "Locations"
    iCounter = 2
    Do While Not myRs.EOF
        Worksheets("sheet2").Range("A" & CStr(iCounter)).Value = myRs(0)
        iCounter = iCounter + 1
        myRs.MoveNext
    Loop
End Sub
myRs.Close
Set myRs = Nothing
myCon.Close
Set myCon = Nothing