Using a Placeholder / Wildcard When Referencing a Filename

[Swoootie]

Hi all,

I hope the solution to what I am doing is fairly simple. Basically I am using VBA to create a dynamic spreadsheet. The basic premise of it is that it will take a date from the user (called 'DT') and use that date to search for 3 files in a specific location to open them up. This is to ensure that only the files relating to the date entered by the user are selected, as these files change on a monthly basis and this would aviod any confusion between dates. So far so good - I have got this working.

The main problem that I am having is in copying across information from the 3 opened sheets onto my main spreadsheet. The methodology that I have tried is detailed in the following example:

'opens up the main sheet
Windows("Monitoring_MainSheet.xls"). _
Activate
Sheets("Stability 2").Select
Range("C10").Select
Windows("ATT_*.xls").Activate
'where *denotes the date suffix as entered by user
ActiveCell.FormulaR1C1 = ("=_*.xls!R3C2")
Range("C11").Select

I have also attempted to redefine the user's entered field, but that doesn't work either.

Please let me know if you would like any further details.

I am really desperate for this section of the code to work, as it will solve a lot of issues. Any help advice would be greatly appreciated.

Thanks

 

[p45cal]

re:"files in a specific location to open them up" & "I have got this working"
You've probably got some code like:
Workbooks.Open "C:\Documents and Settings\myFile.xls"
if you change this to:
Set wkbk1 = Workbooks.Open("C:\Documents and Settings\myFile.xls")
then wkbk1 is that workbook (open file). You can refer to it with the likes of:
wkbk1.sheets("sheet2").range("A3").value
etc.etc.

 

[Swoootie]

Thanks for that explaination. I have tried out your suggestion, but I am still having problems copying across information from the opened sheet to the main sheet (though I know the syntax to do this where the sheet's name is known, I don't know how to do this for where the sheet's name is not known.

Here's the approch that I have used:

Dim WKBOOK1 As Workbook
Dim strFilePath As String
Dim DT As Date

DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

strFilePath = "C:\DOCUMENTS AND SETTINGS\"

Set WKBOOK1 = Workbooks.Open(strFilePath & "STABILITY_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".xls")


Windows( _
"MAIN SPREADSHEET.xls"). _
Activate
Sheets("Stability 2").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = "=[WKBOOK1]!R3C2"
Range("C11").Select

Is there a way around this? Please let me know if you require any further details.

Thank you for your help

 

[Swoootie]

I've ameded the above slightly but it still doesn't run as expected (though it's ceased erroring). Something that I forgot to mention is that because the Excel outputs are automatically generated through SAS (a statistical programming language), the tab names are the same as the file names. I have updated the code to include the tab name from where the data is to be pulled from.

Amemded code as follows:

Dim WKBOOK1 As Workbook
Dim strFilePath As String
Dim DT As Date

DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

strFilePath = "C:\DOCUMENTS AND SETTINGS\"
strFileName = "STAB_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)

Dim WKBOOK1 As Workbook
Dim strFilePath As String
Dim DT As Date

DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

strFilePath = "C:\DOCUMENTS AND SETTINGS\"

Set WKBOOK1 = Workbooks.Open(strFilePath & "STABILITY_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".xls")


Windows( _
"MAIN SPREADSHEET.xls"). _
Activate
Sheets("Stability 2").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = "=[WKBOOK1]strFileName!R3C2"
Sheets("Characteristic Stability 2").Select
Range("C11").Select

I know that the problem lies with the way that the code is trrying to handle the relationship between the 2 spreadsheets, as when the code has finished running the C:\ drive opens up and it asks for me to select a file to reference. The cell that I want the copied value to appear in displays the syntax but contains a #REF! message instead of the value required.

I hope this provides the information that you will need to understand teh full extent of the problem, however, please let me know if you require any futher information on this.

Thanks again.

 

[Swoootie]

Thank you p45ca I have finally (I think) got this working by using:
Windows( _
"Main Spreadsheet"). _
Activate
Sheets("Stability 2").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = WKBOOK1.Sheets(strFileName).Range("a2").VALUE
Sheets("Stability 2").Select
Range("C11").Select

Thnak you so much for your help - you're a life saver. It isn't fully sorted yet, as I still have a lot of work to do on this, but this is enough to get me started.

 

[p45cal]

You need a sheet reference as well as a workbook reference in the link you're trying to add.
Is there only one sheet in the source workbook? You neither know the name of the workbook NOR the name of the sheet you want data from?
The best solution depends on the above Qs and whether you want to copy more data across (you definitely want a link there, not just the value copied over?).
There is a .copy, Paste Link:=true solution too but try the following: Instead of:

Windows("MAIN SPREADSHEET.xls").Activate
sheets("Stability 2").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = "=[WKBOOK1]!R3C2"

try:

WKBOOK1.Activate 'not needed if you've just opened the book as it's already the [COLOR=#0000ff]active [/COLOR]workbook.
Workbooks("MAIN SPREADSHEET.xls").sheets("Stability 2").Range("C10").FormulaR1C1 = "=" & [COLOR=#0000ff]Active[/COLOR]Sheet.Range("B3").Address(ReferenceStyle:=xlR1C1, external:=True)

Edit post posting: This was in answer to message #3 - you posted more since then.

 

[p45cal]

Thank you p45ca I have finally (I think) got this working by using:
Windows( _
"Main Spreadsheet"). _
Activate
Sheets("Stability 2").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = WKBOOK1.Sheets(strFileName).Range("a2").VALUE
Sheets("Stability 2").Select
Range("C11").Select

Thnak you so much for your help - you're a life saver. It isn't fully sorted yet, as I still have a lot of work to do on this, but this is enough to get me started.

Then a single line is all you need:

Workbooks("Main Spreadsheet").sheets("Stability 2").Range("C10").Value = WKBOOK1.sheets(strFileName).Range("a2").Value

 

[Swoootie]

Wow! That's a much quicker and easier way of doing things. Thank you so much. Can't believe how complicated I was making it!

Thanks again