VBA code to save a file to a path contained in a cell

[DBDingo]

Hello All,
Thank you in advance for taking the time to at least read this, any help would be really appreciated as I am really stumped with this one. I am not well versed in VBA coding, I admit I generally find "something close to what I want to do" then massage it to work for me, modifying paths etc.
I have a series of different spread sheets ( 5 in total) that I need to save both a .CSV and a .XLSM version of based upon the "Customer name" in a given cell. This I have working perfectly using the full path reference entered into each "Save" macro. I need the folder containing all the spreadsheets and auto save locations to be portable between computers ( simply saved to the desktop) so for convenience ( so I thought!) I also generated a spreadsheet which through a series of inputs into various cells, I end up with the complete path for both version saves in two separate cells of each worksheet. I want to do this so when the folder is transferred to different machines, all the user has to do is change the "user name" ( eg, from David to John) and the path would automatically update so all the save macros work on their machine. I have these cells formatted as Text. This I actually have working properly also.
What I cannot for the life of me get to work is using the cell with this full path in in, to work in my code
I have even moved the reporting cells to the "Input Data" sheet of my workbook which isn't ideal, but still no joy. I have done a copy and paste of values only, in case it was the formula causing the problem, but that doesn't work either.

Here is my code that works fine:-

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub CSVSave()
Sheets("Input").Select
Rows("7:105").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Dim Path As String
Dim FileName As String

Application.DisplayAlerts = False

Path = "C:\Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV" 'Change the directory path here where you want to save the file
FileName = Range("ak1") & ".CSV" 'Change extension here
ActiveWorkbook.SaveAs Path & FileName, xlCSV 'Change the format here which matches with the extention above. Choose from the following link http://msdn.microsoft.com/en-us/libr.../ff198017.aspx

Application.DisplayAlerts = False

Call Module1.XMLSSave

ActiveWorkbook.Close
Sheets("Input Data").Select
Range("A5").Select

End Sub</code>
what I really need is eg Path=Range("A1").text ( .value?) 'A1 being where the output of the "address sheet" reports to

Ideally I would like to have a situation where the information is being drawn from a separate sheet eg Path=Sheets("Data").range("A1").text (.value?)

when I use this as the Path , it runs without crashing, like it is actually saving, yet nothing ( that I can find anyway) is actually being saved.


I hope this makes sense.

Anyone taking the time to respond, thank you so much in advance.

 

[Coding4Fun]

In your filename what is store in cell AK1?

Also what is your call to Module1.XMLSave doing?

When you say you want the user to change the username, where is this being stored/changed?

I think there is a typo here as well
Path = "C:\Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV\"

 

[DBDingo]

The spreadsheets are part of a furniture manufacturing package I have produced. They take in simple Length, Width and Height inputs and output an entire cutting list of separate components required to produce the cabinets, as well as component identification stickers for each individual component.

Cell AK1 looks at two other cells, a Customer Name and a Job Reference cell and pulls that information together produce a filename eg John Smith and JS001 would then become John_Smith_J001 which then becomes the name of the saved file.

The call to Module1 is simply another Save Macro that saves a Macro Enabled Worksheet of the current file, I do this so I have easy access to the inputs that produced the cutting list just in case something needs to be modified because the customer has changed their mind about something.

I save a .CSV file as the Optimiser program that produces the actual Board cutting details can import it so i don't have to manually re-enter data again at that stage of the workflow.

I have a separate workbook that I call Addresses, it contains cells like

A2 Drive Directory eg C:/User/
A3 User Name eg David/
A4 Folder Location eg Desktop/
A5 Folder Name eg WorkRelated/

and so forth

I then have these cells being referenced on Sheet2 along the lines of =A2&A3&A4 etc to produce the unique path for each of the 5 spreadsheets to have both a CSV and an XLSM version of itself saved. Because all the files are contained in one folder, when another user saves that folder to their desktop, all they need to do is ( or should be) open this one sheet, change A3 to their name and the rest will happen automatically. All of this functions perfectly, I just can't make the Macro then use that automatically generated path to save the files.
On each sheet, I reference back to the 2 cells of the Address workbook that contain the entire assembled path, then that cell is copied to another in the active worksheet as text so the macro is only looking at the active workbook, but still I cannot make it work.

 

[Coding4Fun]

If I am understanding this correctly then it sounds like you are duplicating the file path.

[COLOR=#333333][FONT=monospace]Path = [/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]"C:\Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV"[/FONT][/COLOR][COLOR=#333333][FONT=monospace] 'Change the directory path here where you want to save the file[/FONT][/COLOR]
[COLOR=#ff0000][FONT=monospace]FileName = Range("ak1") & ".CSV"[/FONT][/COLOR][COLOR=#333333][FONT=monospace] 'Change extension here[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]ActiveWorkbook.SaveAs [/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]Path & FileName[/FONT][/COLOR][COLOR=#333333][FONT=monospace], xlCSV 'Change the format here which matches with the extention above. Choose from the following link 
[/FONT][/COLOR]

Your call to SaveAs would look something like this

Path
FileName

C:\Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV C:Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV.CSV

Let me know if this does not help you resolve the problem.
Also as mentioned before I think the file path should have a \ at the end

Say I was putting a file on the desktop called File.CSV

C:Users\David\Desktop\File.CSV

Otherwise it would be

C:Users\David\DesktopFile.CSV

 

[DBDingo]

Thank you for your responses, but you are not actually getting what I am meaning. The code as it is runs and functions perfectly, on my computer, when it moves to another computer, it ceases to function because the "user name" of the new computer changes, This is why I need the dynamic references operating.


C:\Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV\ functions perfectly on my machine, but it the use name on the next machine was eg. Paul, then obviously it doesn't.

So say the path being generated by my Address workbook was residing in Sheet3 Cell A1 of the current active workbook I am trying to save.

What I am actually looking for is how to make the code read that cell as the path name.

eg something like Path =Sheets("Sheet3").Range("A1").Text I use this actual line of code to give me the filename in other sheets and that works faultlessly, I don't understand why it doesn't work with the file path also.

By having Path reference that cell, my theory is, when the user changes the "user name" from David to Paul in the address workbook, the dynamic link will then read

C:\Users\Paul\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV instead of

C:\Users\David\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV and the save buttons would then function the same as they do on my machine.

I hope this makes more sense to you now. I really appreciate you trying to help, thank you.

 

[Coding4Fun]

Maybe try capturing the username separate and then do something like this.

Put the username in field AK2, Also the other users would need to have this same filepath setup.
They need to have a path same as you leading to
C:\Users\Username\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV

Any deviation in the naming will make it not work.

[COLOR=#333333][FONT=monospace]Sub CSVSave()[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Sheets("Input").Select[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Rows("7:105").Select[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Selection.Copy[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Workbooks.Add[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]:=False, Transpose:=False[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Range("A1").Select[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Application.CutCopyMode = False[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Dim Path As String[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim FileName As String
[/FONT][/COLOR]Dim Username1 as String
Dim CombFileName as String

[COLOR=#333333][FONT=monospace]Application.DisplayAlerts = False
[/FONT][/COLOR]Username1 = Range("AK2").Value
CombFileName  = Range("AK1").Value & Range("AK2").Value & ".csv"

[COLOR=#333333][FONT=monospace]Path = "C:\Users\" & Username1 & "\Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV" 'Change the directory path here where you want to save the file[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]FileName = CombFileName 'Change extension here[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]ActiveWorkbook.SaveAs Path & FileName, xlCSV 'Change the format here which matches with the extention above. Choose from the following link [/FONT][/COLOR][URL="http://msdn.microsoft.com/en-us/library/office/ff198017.aspx"]http://msdn.microsoft.com/en-us/libr.../ff198017.aspx[/URL]

[COLOR=#333333][FONT=monospace]Application.DisplayAlerts = False[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Call Module1.XMLSSave[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]ActiveWorkbook.Close[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Sheets("Input Data").Select[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Range("A5").Select[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]End Sub[/FONT][/COLOR]

 

[DBDingo]

OK,we seem to be getting closer, thank you.
When I ran the code you provided, it threw up a runtime error for some reason. I modified it slightly and it works down to the ActiveWorkbook.SaveAs ---------

My Customer name for the purpose of testing is Test 1a the code as it is now opens a new worksheet actually named this, so that part is working correctly.

At the above mentioned line of code execution a warning message appears on the screen
'Test1a.csv' cannot be accessed.The file may be corrupted,located on a server that is not responding, or Read Only

Sub CSVSave()
 Sheets("Input").Select
 Rows("7:105").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    
Dim Path As String
Dim FileName As String
Dim UserName As String


Application.DisplayAlerts = True
UserName = Range("AK2").Value


Path = "C:\Users\" & UserName & " \Desktop\Work Related\Customer File Auto Save\5 Scrap Run Stickers\CSV\" 'Change the directory path here where you want to save the file
FileName = Range("ak1") & ".CSV" 'Change extension here
ActiveWorkbook.SaveAs Path & FileName, xlCSV 'Change the format here which matches with the extention above. Choose from the following link http://msdn.microsoft.com/en-us/library/office/ff198017.aspx


Application.DisplayAlerts = False
 
Call Module1.XMLSSave


ActiveWorkbook.Close
Sheets("Input Data").Select
 Range("A5").Select


End Sub

Thank you for assisting me with this, I really do appreciate it.

* if I knew how to include the workbook I would as it may make things clearer.

 

[DBDingo]

I now have the issue solved. Thanks to your kind suggestions I have the full functionality I need to solve my "portability issue" I cannot thank you enough for your assistance. Have a great day mate.

 

[Coding4Fun]

Glad I could help steer you in the right direction