Sorry for my delay in responding.
I'm including my coding below.....thank you in advance for looking over this
for me.
Option Compare Database
Public Function Import()
Dim FileNamePart1 As String
Dim FileNamePart2 As String
Dim strFile As String
Public Const FileNamePart1 As String = "\\Midpechsap01\echsxlrpt\"
Public Const FileNamePart2 As String = "\BacklogAgingSummary\ECHS - Backlog
_Aging Summary.xls"
fullPathName = FileNamePart1 & Format(Now() - 1, "yyyy-mm-dd") & FileNamePart2
' put your code here to use the TransferText method
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel8, _
TableName:="ECHS Bklg", _
FileName:=FileNamePart1 & FileNamePart1 & strFile, _
HasFieldNames:=True
End Function
--
JCarter
ALWAYS Learning
"Jim Burke in Novi" wrote:
> I'd have to see the code that you're getting the error on. If you tried
> putting the Constant declarations in a sub or function, I'm pretty sure that
> won't work. I think they have to be defined at the module level. If they are
> in a form module, they have to be declared as private. If that's not your
> error, I'd need to see the code generating the error.
>
> "JCarter" wrote:
>
> > Thank you for your response....
> > I did as you noted and I'm receiving a compile error, "Invalid attribute in
> > Sub or Function"
> >
> > I KNOW it's the operator....any suggestion?
> > --
> > JCarter
> > ALWAYS Learning
> >
> >
> > "Jim Burke in Novi" wrote:
> >
> > > Something like this should work:
> > >
> > > Public Const fileNamePart1 As string = "\\ShareName\Folder1rpt\"
> > > Public Const fileNamePart2 As string = "\Folder2Summary\FileName.xls"
> > >
> > > Put those in a module where constants are allowed. Then in your VBA code
> > > where you do the import:
> > >
> > > fullPathName = fileNamePart1 & Format(now()-1, "yyyy-mm-dd") & fileNamePart2
> > >
> > > "JCarter" wrote:
> > >
> > > > On a daily bases, we import a file from our server. It's not the file name
> > > > that chgs each day, but one of the folders. It's always named as the
> > > > previous day's date (YYYY-MM-DD)
> > > >
> > > > What type of coding would I use to maybe include a formula such as =Now()-1,
> > > > so that I manually don't have to chg the folder name each day? Below is the
> > > > path currently used. The standalone date is the folder that chgs each day..
> > > >
> > > > \\ShareName\Folder1rpt\2008-07-02\Folder2Summary\FileName.xls
> > > >
> > > > Thank you in advance!!!
> > > > --
> > > > JCarter
> > > > ALWAYS Learning
|
Similar Threads
