Use VBA to Specify New Source Filename and Location

[DrDave6]

I have a folder with around 40 spreadsheets, and each has a OLEDB Connection. I've written a macro that process each spreadsheet in the folder, and that works!

I'm need to change the source filename, and location, using VBA.

Assume the following:

- Connection Name is "ThePrices"
- new filename is "NewPrices.xlsx"
- new filelocation is "c:\Users\David\Documents\MySourceFiles"

All help is appreciated....

 

[royUK]

This will allow the user to select a file

Option Explicit




Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
on error goto err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)


    Workbooks.Open Filename:=sWb
Exit Sub
err_handler:
Msgbox "No selection made"
End Sub

 

[DrDave6]

This will allow the user to select a file

Option Explicit




Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
on error goto err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)


    Workbooks.Open Filename:=sWb
Exit Sub
err_handler:
Msgbox "No selection made"
End Sub

Did you mean to post this for another thread. I'm looking for the VBA code that I can use to change the source filename and location. There is no user interaction.

 

[royUK]

How else would you change the file?

Without seeing the code it's not clear at all what you mean.

 

[DrDave6]

Sorry for the confusion royUK...

There is no code, yet. Imagine that i have a workbook open, and, using VB, I want to change the name and location of an external source file, that I'm currently using in an OLEDB connection. I provided the details in my original message that could be used in the example.

 

[royUK]

You cannot change the code whilst it is running.