Filenames in macros

[ramboia]

Hi I need some help.


I´m beginning in excel macros. So I make a macro that copies some data
sheets from a file in a floopy disk to my current workbook, after then
make a copy to my backup directory and works fine.

But every day I have to edit the macro, because every day the file in
the floppy change and my current wokbook change too, so I would like
costumize, to make a variabe of the names the files:



"A:\STA_FQI_PI-2004-02-07.xls" and "c:\localdocs\today\Relatorio
07.02.2004.xls"


If anyone can help or suggest I´ll say thanks.


Thanks for all

Ramboia


This is my code:

Sub Charge_floopy()



Workbooks.Open Filename:="A:\STA_FQI_PI-2004-02-07.xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Name", "Volumes", "values", "Temperaturas")). _
Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Range("A1:AB120").Select
Selection.Copy
Windows("Relatorio 07.02.2004.xls").Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("I", "II", "III", "IV")).Select
Sheets("I").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Lote 1").Select
Range("A1").Select
Application.CutCopyMode = False
ChDir "c:\localdocs\today"
ActiveWorkbook.SaveAs Filename:= _
"c:\localdocs\today\Relatorio 07.02.2004.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWindow.Close
End SubEnd Sub

 

[Colo]

Hello ramboia,
Assuming the file names depends on the today's date, you can make th
changing part with Format function.


Code
-------------------

Dim A_Name As String
Dim C_Name As String
A_Name = "A:\STA_FQI_PI-" & Format(Date, "yyyy-mm-dd") & ".xls"
C_Name = "c:\localdocs\today\Relatorio " & Format(Date, "dd.mm.yyyy") & ".xls"
Workbooks.Open Filename:=A_Name
'Omit
ActiveWorkbook.SaveAs Filename:=C_Name, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

 

[rodt]

If there is only one workbook on the floppy disk, then perhaps you coul
look at the files on the floppy using the file scripting object an
automatically determine the workbook name.

The next step is to create the current workbook name, either from th
name on the floppy disk or by whatever logic you use to set it now, o
perhaps even, search again using the file scripting object. Jus
guessing, something like
sWbkNamOut = "c:\localdocs\today\Relatoria "
format(Now,"dd.mm.yyyy") & ".xls"
might be sufficient.

Then it is just a matter of changing the lines that open the workbook
from what you have now to use the variables.
Note also that the chdir command not required if the output workboo
filename has the path