Import Paths

[D]

Hi:

I have a macro that Imports two Excel files from the same folder:
ie: U:\\A\B\Januaray 2008\1.xls and U:\\A\B\Januaray 2008\2.xls; simple and
fine...

If you could tell me haw do I do this I would I appreciate it:

Until the month, the path is always the same; but in Feb. January will be
replaced with February 2008; the files names are the same; is there any
'easy' way not to change the path in the macro avery month, but dinamically
determine the new path?

Thanks,

Dan

 

[Klatuu]

Not with a Macro, easily. You could use VBA instead. Here is an example
that will build the path for you based on the month:

Public Function MonthlyPath(dtmPathDate) As String
MonthlyPath = "U:\\A\B\" & Format(dtmPathDate, "mmmm, yyyy") & "\1.xls"
End Function

 

[D]

Thank you Dave!

Can I call this function before the macro runs? How do I do for the 2.xls
file?

ie: call MonthlyPath(dtmPathDate)
docmd.runmacro "mymacro", 1

Thanks gain,

Dan

 

[Klatuu]

The macro would not be able to find it. You may try calling it from macro's
File Name property for the TransferSpreadsheet Action, but since I don't use
Macros, I don't know if that will work. You could use VBA for the whole
thing rather than a macro.

 

[D]

Hi Dave:

What I meant was; so I crated a module:

Option Compare Database

Public Function MonthlyPath(dtmPathDate) As String
MonthlyPath = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(dtmPathDate, "mmmm, yyyy") &
"\mthly_journals_99124.xls"
MonthlyPath = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(dtmPathDate, "mmmm, yyyy") &
"\mthly_journals.xls"

End Function

In my form, on open, I will have:

call Module2.MonthlyPath
DoCmd.RunMacro "macro-Import-jnls", 1

Is that right? I get 'Argument not optional' on call?

Thanks,

Dan

 

[Klatuu]

You can't assign two values to a function. Are you outputting two
spreadsheets or are wanting to change the file based on some condition?

In either case that will not work. Tell me how you decide which file to
output and I can show you how to do it in VBA without using a macro.

 

[D]

Hi Dave:

I am importing two Excel files from the same location in the same table:
January 2008; U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\January 2008; In February the the will
be February 2008

The names of the files will be the same; but in Feb, March, etc the last
folder changes.
Thanks,

Dan

 

[Klatuu]

Here is code that will do what you want.
Dim strImportSetUp As string
Dim strImportPath As String

strImportSetUp = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008Autorec\4Alice\" & Format(Date,"mmmm, yyyy") & "\$.xls"

strImportPath = Replace(strImportSetUp, "$", "1")
Docmd.TransferSpreadsheet acImport, , "QueryName1", strImportPath, True
strImportPath = Replace(strImportSetUp, "$", "2")
Docmd.TransferSpreadsheet acImport, , "QueryName2", strImportPath, True

 

[D]

Excellent!

THANKS A LOT, Dave!!!

Dan

Here is code that will do what you want.
Dim strImportSetUp As string
Dim strImportPath As String

strImportSetUp = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008Autorec\4Alice\" & Format(Date,"mmmm, yyyy") & "\$.xls"

strImportPath = Replace(strImportSetUp, "$", "1")
Docmd.TransferSpreadsheet acImport, , "QueryName1", strImportPath, True
strImportPath = Replace(strImportSetUp, "$", "2")
Docmd.TransferSpreadsheet acImport, , "QueryName2", strImportPath, True

 

[Klatuu]

Glad it worked. There is one issue, however, I should make you aware of. In
the original code, I put a comma in that should not be there:

In this line:
strImportSetUp = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008Autorec\4Alice\" & Format(Date,"mmmm, yyyy") & "\$.xls"

You may want to remove the comma after the mmmm

 

[D]

Thanks Dave! I saw it and changed, without; and also the \ , between 2008 and
Autorec.

In the same note I think we can go 'farther' and format the 2008 folder, for
any year, as: Format(Date,'yyyy") to pick up any year?

Thanks and have a nice day!

Dan

 

[Klatuu]

Damn newreader doesn't have syntax checking.
I wish they would fix that

 

[D]

Cheers!

Thanks,

Dan

Damn newreader doesn't have syntax checking.
I wish they would fix that