Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2021, 07:38 AM
SoMany SoMany is offline Copying sheet from file round 2!!! Windows 7 64bit Copying sheet from file round 2!!! Office 2016
Advanced Beginner
Copying sheet from file round 2!!!
 
Join Date: Oct 2016
Posts: 43
SoMany is on a distinguished road
Default Copying sheet from file round 2!!!

So I've made enough progress in VBA to post this again. I'm trying to copy a sheet from another file in another folder. I can now make macros that copy data from another file within a file, but don't know how to copy the sheet when it's in another file.



Would someone modify this or say what code I should use?

The path for the other folder would be
"C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2"
Attached Files
File Type: xlsm MacroTest.xlsm (20.4 KB, 6 views)
Reply With Quote
  #2  
Old 01-11-2021, 10:11 AM
p45cal p45cal is offline Copying sheet from file round 2!!! Windows 10 Copying sheet from file round 2!!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 437
p45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of light
Default

try changing:
Code:
    filename = ThisWorkbook.Path & Application.PathSeparator & "TestImportFile1.xlsx"
to the likes of:
Code:
    filename = "C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"
Reply With Quote
  #3  
Old 01-11-2021, 11:03 AM
SoMany SoMany is offline Copying sheet from file round 2!!! Windows 7 64bit Copying sheet from file round 2!!! Office 2016
Advanced Beginner
Copying sheet from file round 2!!!
 
Join Date: Oct 2016
Posts: 43
SoMany is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
try changing:
Code:
    filename = ThisWorkbook.Path & Application.PathSeparator & "TestImportFile1.xlsx"
to the likes of:
Code:
    filename = "C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"


Ok, so that worked, but this file will be used by others and kept in their folders under different user names. I tried something like;


filename = "C:\Users" & uzer & "\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"


And it did not work in my profile. The spreadsheet must be usable in other users profiles.
Reply With Quote
  #4  
Old 01-11-2021, 11:42 AM
p45cal p45cal is offline Copying sheet from file round 2!!! Windows 10 Copying sheet from file round 2!!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 437
p45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of light
Default

filename = "C:\Users\" & uzer & "\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"
Reply With Quote
  #5  
Old 01-12-2021, 06:11 AM
SoMany SoMany is offline Copying sheet from file round 2!!! Windows 7 64bit Copying sheet from file round 2!!! Office 2016
Advanced Beginner
Copying sheet from file round 2!!!
 
Join Date: Oct 2016
Posts: 43
SoMany is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
filename = "C:\Users\" & uzer & "\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"



Even with the change it's not finding the folder. The new macro is below.


Edit; and for some reason I added an extra "f" in the original post in "DifferentFolderf"....
Reply With Quote
  #6  
Old 01-12-2021, 08:44 AM
p45cal p45cal is offline Copying sheet from file round 2!!! Windows 10 Copying sheet from file round 2!!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 437
p45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of light
Default

1. No macro
2. the variable uzer should contain something; is it the right thing?
Reply With Quote
  #7  
Old 01-12-2021, 02:35 PM
SoMany SoMany is offline Copying sheet from file round 2!!! Windows 7 64bit Copying sheet from file round 2!!! Office 2016
Advanced Beginner
Copying sheet from file round 2!!!
 
Join Date: Oct 2016
Posts: 43
SoMany is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
1. No macro
2. the variable uzer should contain something; is it the right thing?
Sorry about that
Attached Files
File Type: xlsm MacroTest.xlsm (20.5 KB, 3 views)
Reply With Quote
  #8  
Old 01-12-2021, 03:15 PM
p45cal p45cal is offline Copying sheet from file round 2!!! Windows 10 Copying sheet from file round 2!!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 437
p45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of lightp45cal is a glorious beacon of light
Default

Nothing assigns any value to uzer. That's why it's not found.
Reply With Quote
  #9  
Old 01-14-2021, 11:01 PM
SoMany SoMany is offline Copying sheet from file round 2!!! Windows 7 64bit Copying sheet from file round 2!!! Office 2016
Advanced Beginner
Copying sheet from file round 2!!!
 
Join Date: Oct 2016
Posts: 43
SoMany is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Nothing assigns any value to uzer. That's why it's not found.
I don't understand the post. What should be the code so the spreadsheet can be used from any users account?
Reply With Quote
  #10  
Old 01-15-2021, 01:48 AM
Purfleet Purfleet is offline Copying sheet from file round 2!!! Windows 10 Copying sheet from file round 2!!! Office 2019
Competent Performer
 
Join Date: Jun 2020
Location: Essex
Posts: 229
Purfleet is a name known to allPurfleet is a name known to allPurfleet is a name known to allPurfleet is a name known to allPurfleet is a name known to allPurfleet is a name known to all
Default

Hi SoMany

Try uzer = Environ("Username")

before the filename assign

Or ,ideally, use a network drive location it will probably be more reliable
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying sheet from file round 2!!! Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas rsrasc Excel Programming 4 08-19-2018 04:00 PM
Copying sheet from file round 2!!! Copying a part of a docx file as a separate file officeboy09 Word 6 09-26-2014 05:15 PM
Copying sheet from file round 2!!! Copying excel charts into word - huge file size. Matt.M Word 2 04-02-2014 07:38 PM
Copying sheet from file round 2!!! Copying data from sheet with deleted columns creates blanks ZGreyArea Excel 1 11-20-2013 10:12 AM
Error Copying file or folder - Drag-N-Drop--> Network Shared Kris.Fulgham Outlook 1 10-17-2005 09:02 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft