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.
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.