PDA

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