VBA Set File path to a cell reference in the workbook

[JonnyNumber]

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]

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]

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]

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]

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]

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]

Hi Paul, Thank you for your help on this. It is now working perfectly for me.

Kind Regards,
John