View Full Version : VBA Set File path to a cell reference in the workbook
JonnyNumber
08-20-2018, 03:47 AM
Hi,
I am relatively new to VBA and am trying to save a file using a file path as determined by a cell reference within the workbook (Cell E3 in the Control Sheet). My code for saving the file is as below (Attempt 1), however, as you can see, it has the file path hard coded in it.
Attempt 1
Sub copy_manager_info()
' copy_manager_info Macro
Sheets("1.Manager_Info").Select
Rows("1:1048576").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileName:= _
"C:\MANAGER_INFO_201809.txt", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close
Sheets("Control Sheet").Select
Range("A1").Select
End Sub
I have also tried the below, using Range("E3").Value
Attempt 2
Sub copy_manager_info()
' copy_manager_info Macro
Sheets("1.Manager_Info").Select
Rows("1:1048576").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileName:= _
"C:\MANAGER_INFO_" & Range("E3").Value & ".txt", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close
Sheets("Control Sheet").Select
Range("A1").Select
End Sub
Any help would be greatly appreciated.
Kind Regards,
JonnyNumbers
Paul_Hossler
08-20-2018, 05:13 AM
1. You can use the [#] icon to insert CODE tags and paste your macro between to format and set it off
2. Not necessary to copy all the rows
3. I find it's easier if I create workbook variable(s) (e.g. wb2) to keep the workbooks straight
4. Try this
Option Explicit
Sub CopyMgr()
Dim wb2 As Workbook
Workbooks.Add
Set wb2 = ActiveWorkbook
ThisWorkbook.Sheets("1.Manager_Info").UsedRange.Copy
wb2.Worksheets(1).Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
' wb2.SaveAs Filename:="C:\MANAGER_INFO_" & Range("E3").Value & ".txt", FileFormat:=xlCSV, CreateBackup:=False
wb2.SaveAs Filename:="C:\users\daddy\desktop\" & Range("E3").Value & ".txt", FileFormat:=xlCSV, CreateBackup:=False
wb2.Close
ThisWorkbook.Activate
Sheets("Control Sheet").Select
Range("A1").Select
End Sub
JonnyNumber
08-20-2018, 07:09 AM
Thanks for the reply Paul, much appreciated. Thank you also for the tips for the forum, this was my first post, I will ensure to enter the tags for code going forward.
I tried the code you suggested, but it doesn't seem to work. The file that I am trying to save is not the active macro enabled workbook, it is in fact a tab within the Macro Enabled workbook, tab called "Manager_Info" (to clarify, there are 2 tabs in the macro enabled workbook, one is called = Control Sheet, which contains the macro buttons and so on, and a second one = Manager Info which houses all of the Manager Information). What I am trying to do is to save the data from the Manager Info tab to a .txt file. As an add on to this, do you perhaps know a way of inserting a Pipe separator rather than a Comma separator into the text file or am I pushing this far beyond the bounds of a Macro?
With regard to it not being necessary to copy all rows, The number of rows differs every time and therefore is it not necessary to copy all rows in this case? the part of the code
ThisWorkbook.Sheets("1.Manager_Info").UsedRange.Copy, does the
.UsedRange.Copy select all the lines that are in use in the Marco enabled workbook?
My Original Code
'1. Manager Info
Sub copy_manager_info()
' copy_manager_info Macro
Sheets("1.Manager_Info").Select
Rows("1:1048576").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileName:= _
"C:\MANAGER_INFO_201803.txt", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close
Sheets("Control Sheet").Select
Range("A1").Select
End Sub
Your Suggested Code
Sub CopyMgr1()
Dim wb2 As Workbook
Workbooks.Add
Set wb2 = ActiveWorkbook
ThisWorkbook.Sheets("1.Manager_Info").UsedRange.Copy
wb2.Worksheets(1).Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
' wb2.SaveAs Filename:="C:\MANAGER_INFO_" & Range("E3").Value & ".txt", FileFormat:=xlCSV, CreateBackup:=False
wb2.SaveAs fileName:="C:\users\daddy\desktop\" & Range("E3").Value & ".txt", FileFormat:=xlCSV, CreateBackup:=False
wb2.Close
ThisWorkbook.Activate
Sheets("Control Sheet").Select
Range("A1").Select
End Sub
I'm so sorry for the number of questions in the reply, but I appreciate your time to look at this for me.
Kind Regards,
JonnyNumbers
Paul_Hossler
08-20-2018, 10:41 AM
I put my macro into a XLSM and added the two sheet with your names
wb2 is the workbook that the macro creates, and wb2.Saveas saves it
Running the macro did product a CSV file to my desktop (you did uncomment out your line and delete mine, right?)
.UsedRange get the range on the worksheet that has been used so far, even if now blank, but not rows that were not used
Pipe char can be used
JonnyNumber
08-20-2018, 01:28 PM
I did uncomment my comment and deleted your filepath. It worked, but the output file name did not contain the value as in E3 in the Macro enabled workbook. Do I need to specify the Workbook name here?
Regards,
JonnyNumbers
Paul_Hossler
08-20-2018, 04:59 PM
Yes, that would probably be more secure
Just using Range() without a workbook specified would use whatever workbook was active
wb2.SaveAs Filename:="C:\MANAGER_INFO_" & Thisworkbook.Range("E3").Value & ".txt", FileFormat:=xlCSV, CreateBackup:=False
I probably had the XLSM active when testing, and just missed it -- sorry
JonnyNumber
08-21-2018, 08:39 AM
Hi Paul, Thank you for your help on this. It is now working perfectly for me.
Kind Regards,
John
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.