• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 770
  • Last Modified:

Access 2007 MACRO: Transfer Spread Sheet

I have a Transfer Spread Sheet macro that imports an excel file and I am wanting to create a file name path that will work for multiple users on my network and off my network.

So, here is what I have tried and it didnt work.

"\\%COMPUTERNAME%\Users\%username%\Documents\Import\file name.xls"
0
CMILLER
Asked:
CMILLER
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try doing this way..
in your macro add a single action (RunCode)
Action
RunCode


Function Name ImportXL()


now create a function in a regular VBA module

Function ImportXL()

dim strFile as string
strFile="\\%COMPUTERNAME%\Users\%username%\Documents\Import\file name.xls"

'complete this line for the TransferSpreadsheet command

docmd.transferspreadsheet acimport,8, "NameOfTable", strFile, true,"NameOfsheet!"

end function
0
 
CMILLERAuthor Commented:
It doesnt like this line even though I added the name of the TABLE and the Name of the Sheet

docmd.transferspreadsheet acimport,8, "NameOfTable", strFile, true,"NameOfsheet!"
0
 
CMILLERAuthor Commented:
It works if I use the actual computer name and username in the path
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Rey Obrero (Capricorn1)Commented:
change this line

strFile="\\%COMPUTERNAME%\Users\%username%\Documents\Import\file name.xls"

with

strFile=environ("userprofile") & "\Documents\Import\file name.xls"
0
 
CMILLERAuthor Commented:
That did it!!

I know that wasnt part of the question but how do I add a notification to the module that shows that the table updated?

Thanks
0
 
Rey Obrero (Capricorn1)Commented:
add this line after the transferspreadsheet  command


msgbox "Table nameOftable was updated!"
end function
0
 
CMILLERAuthor Commented:
It worked, Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now