PDA

View Full Version : Solved: Different terms for saving a file



Rejje
11-20-2010, 03:21 AM
Hi!

Here’s my problem which should be in a module:


I want the macro to check if active workbook is called “C:\path1\Template.xslm”

IF SO:
Save file as “C:\path2\x.xlsm” (the “x” is a value found in a cell named “EXP_01”)
Close “C:\path1\Template.xslm” without saving anything
Open “C:\path2\x.xlsm”

IF NOT:
Save file as usual


Anyone got an idea?

Kenneth Hobs
11-20-2010, 10:43 AM
Sub SaveThis()
Dim wb As Workbook
Set wb = ActiveWorkbook
If UCase(wb.FullName) = UCase("C:\path1\Template.xslm") Then
ActiveWorkbook.SaveAs "C:\path2\" & Range("EXP_01").value & ".xlsm"
Workbooks.Open wb.FullName
Else
wb.Save
End If
Set wb = Nothing
End Sub

Rejje
11-20-2010, 11:36 AM
Sub SaveThis()
Dim wb As Workbook
Set wb = ActiveWorkbook
If UCase(wb.FullName) = UCase("C:\path1\Template.xslm") Then
ActiveWorkbook.SaveAs "C:\path2\" & Range("EXP_01").value & ".xlsm"
Workbooks.Open wb.FullName
Else
wb.Save
End If
Set wb = Nothing
End Sub

Works perfectly! Can't really express how greatful I am for your help and for this forum!

Rejje
11-20-2010, 01:04 PM
Works perfectly! Can't really express how greatful I am for your help and for this forum!


OK - ONE MORE THOUGH!

I now have the full searchstring now for "C:\path1\Template.xslm" in a cell named EXP_02 as well as the full searchstring for C:\path2\" & Range("EXP_01").Value & ".xlsm in a cell named EXP_03 (never mind the EXP_01 now).

The reasons for this is they are both variables depending on if I'm at home or at work and saving the template as "abc.xlsm" is depending on what text has been fed into it.

This is an idea of what I mean:



Sub SaveThis()
Dim wb As Workbook
Set wb = ActiveWorkbook
If UCase(wb.FullName) = UCase(read complete searchsting in EXP_02)Then
ActiveWorkbook.SaveAs "read complete searchsting in EXP_01"
Workbooks.Open wb.FullName
Else
wb.Save
End If
Set wb = Nothing

'Sub SaveThis()
' Dim wb As Workbook
' Set wb = ActiveWorkbook
' If UCase(wb.FullName) = UCase("C:\path1\Template.xslm") Then
' ActiveWorkbook.SaveAs "C:\path2\" & Range("EXP_01").Value & ".xlsm"
' Workbooks.Open wb.FullName
' Else
' wb.Save
' End If
' Set wb = Nothing
'End Sub

End Sub

Rejje
11-20-2010, 01:18 PM
NOTE! It's hard trying to learn vba with a 1,5 year old pulling one's shirt all the time so correction follows:

This is an idea of what I mean:



Sub SaveThis()
Dim wb As Workbook
Set wb = ActiveWorkbook
If UCase(wb.FullName) = UCase(read complete searchsting in EXP_02)Then
ActiveWorkbook.SaveAs "read complete searchsting in EXP_03"
Workbooks.Open wb.FullName
Else
wb.Save
End If
Set wb = Nothing


End Sub

Kenneth Hobs
11-20-2010, 04:37 PM
I not am sure that I understand. What I generally do when working from different folders, I use Dir() to determine if the filename or directory exists.

Going by what you last posted:
Sub SaveThis()
Dim wb As Workbook
Set wb = ActiveWorkbook
If UCase(wb.FullName) = UCase(wb.Range("EXP_02").Value2 Then
ActiveWorkbook.SaveAs UCase(wb.Range("EXP_03").Value2
Workbooks.Open wb.FullName
Else
wb.Save
End If
Set wb = Nothing
End Sub

Rejje
11-21-2010, 04:04 AM
I think you understood correctly. However I get "Compilation error: Syntax error" (red). For different reasons I have renamed the cells from "EXP_0X" to "V_50X00" as you see below.

Text in cell V_50100 = "C:\Documents and Settings\RejjeRejje\Skrivbord\Post\Post template\Post template.xlsm"

Text in cell V_50200 = "C:\Documents and Settings\RejjeRejje\Skrivbord\Post\Poster\POST-5129658715.xlsm"



Sub SaveThis()
Dim wb As Workbook
Set wb = ActiveWorkbook
If UCase(wb.FullName) = UCase(wb.Range("V_50100").Value2 Then
ActiveWorkbook.SaveAs UCase(wb.Range("V_50200").Value2
Workbooks.Open wb.FullName
Else
wb.Save
End If
Set wb = Nothing
End Sub


What can be the issue here?