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: 51
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, 10 views)
Reply With Quote
  #2  
Old 01-11-2021, 10:11 AM
p45cal's Avatar
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: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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: 51
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's Avatar
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: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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: 51
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's Avatar
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: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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: 51
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, 13 views)
Reply With Quote
  #8  
Old 01-12-2021, 03:15 PM
p45cal's Avatar
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: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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: 51
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
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
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
  #11  
Old 01-21-2021, 07:45 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: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
Hi SoMany

Try uzer = Environ("Username")

before the filename assign

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

That did the trick. Thank you.
Reply With Quote
  #12  
Old 01-23-2021, 09:02 AM
Artik Artik is offline Copying sheet from file round 2!!! Windows 10 Copying sheet from file round 2!!! Office 2019
Novice
 
Join Date: Jun 2019
Location: Poland
Posts: 4
Artik 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"
Hi p45cal. I do not recommend using such a "rigid" solution when referring to Desktop. For me, the path to Desktop is:
"C:\Users\<prifile_name>\SkyDrive\Pulpit". (Polish version )
I rather recommend it:
Code:
Sub AAA()
    MsgBox GetDesktopPath & "Test import file\DifferentFolder\TestImportFile2\"
End Sub


Function GetDesktopPath()
    Dim WSHShell    As Object

    Set WSHShell = CreateObject("Wscript.Shell")
    GetDesktopPath = WSHShell.SpecialFolders(4)
    If Right(GetDesktopPath, 1) <> Application.PathSeparator Then
        GetDesktopPath = GetDesktopPath & Application.PathSeparator
    End If
End Function
Artik
Reply With Quote
  #13  
Old 02-26-2021, 10:19 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: 51
SoMany is on a distinguished road
Default

This has been amazing help everyone. One last thing. When pasting/copying into another sheet, I keep getting a message that says the information I want to post is large and wants to know if I want to paste the information into another clipboard later.


Any way to get the the macro to automatically click yes?
Reply With Quote
  #14  
Old 02-27-2021, 07:54 AM
p45cal's Avatar
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: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I'm not sure if this will work.
Step through your code with F8 on the keyboard until the message pops up and make a note of which line caused it, then edit the macro to include the line:
Code:
Application.DisplayAlerts = False
just before it, and later add the line:
Code:
Application.DisplayAlerts = True
to restore it so that future alerts appear as normal (this applies to the user interface messages too, such as when deleting a sheet manually).
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

All times are GMT -7. The time now is 05:57 AM.


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