CODEWITHSUNDEEP
excelvbafilenamesfilepath
Doug Coats
Doug Coats

Reputation: 7117

Setting FilePath and FileName in vba

Ok so I think I have gotten confused on how to do this and maybe I am doing something obviously wrong?

Here is my code :

Private Sub CommandButton1_Click()
    Dim wbI As Workbook, wbO As Workbook, wb1 As Workbook
    Dim wsI As Worksheet, wsO As Worksheet, ws1 As Worksheet

    filelocation1 = "C:\Users\Public\Desktop_" & Format(Date, "ddmmyyyy") & "Production.xls"
    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("Production")
    Set wbO = Workbooks.Add
    With wbO
        Set wsO = wbO.Sheets("Sheet1")
        ActiveWorkbook.SaveAs Filename:=filelocation1 '******
        wsI.Range("A1:C100").Copy
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
End With

However at the asterisked line I get a run time error (included as image). I think it's because I am saving the file incorrectly...but I thought this was correct? Is it because I am using a file path instead of a file name? Would I need ot add at the end of the path "Name".xls? Or do I need to separate file name and file path?

Thanks in advance :)

enter image description here

EDIT: Now Things are working however, the file is being dumped in "C:\Users\Public" location with DESKTOP added to the name (instead of "C:\Users\Public\Desktop" . I have tried all sorts of stuff trying to route it to the correct location, like adding a "_" at the end as well as other tricks that have not worked.

How do i fix this?

SOLVED : 

filelocation1 = "C:\Users\Public\Desktop" & "\" &  Format(Date, "ddmmyyyy") _ 
& "Production.xls"

Upvotes: 2

Views: 61996

Answers (2)

Arazio
Arazio

Reputation: 295

Syntax error i believe

I tend to use something like

dim filelocation as string
dim fileext as string
dim filestring as string

Then use "C:\Users\Public\Desktop\" as your file location and set the fileext as whatever you like e.g. if you wanted to say type you own file name into a text box then fileext = textbox.text then it's a case of filestring = filelocation & fileext. This sort of method allows you flexibility if you play around with it

Upvotes: 4

Jordan
Jordan

Reputation: 4514

You need to include the file name in the file path e.g. "C:\Users\Public\Desktop\Name.xls"

Upvotes: 4

Related Questions