Coding for TransferSpreadsheet

  • Thread starter Thread starter JCarter
  • Start date Start date
J

JCarter

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!!!
 
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
 
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?
 
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.
 
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
 
Back
Top