#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Saving excel file into a created file using Access VB


    Hoping somebody can help.
    I am opening an excel file through access and manipulating it and saving it into a specific file. However, I am creating this file using access form data and saving the excel file with the same data. I get a Run-time Error '13' Type Mismatch. It looks like a syntax error to me but I'm not sure. Here's my code so far.

    If Dir("D:\Clients\" & Me.Job_No & " " & Forms![Project] & " LF", vbDirectory) = "" Then
    MkDir ("D:\Clients\" & Me.Job_No & " " & Forms![Project] & " LF")
    End If


    This part works fine and I can get the file created with the lable I want. Next I open the excel spreadsheet and add access data.


    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("D:\Report Templates\Report1.xls")
    objXLApp.Application.Visible = True
    objXLBook.ActiveSheet.Range("Z1") = Me.Job_No


    This code works as well. However, When I try to name and save this excel file into the created folder, it will not work. Here is the code I have been working with.

    objXLBook.SaveAs ("D:\Clients\" & Me.Job_No & " " & Forms![Project] & " LF" \ Me.Job_No & " " & Forms![Project] & " LF Report.xls")
    End Sub


    I have tried the following

    Folder_Name = (Me.Job_No & " " & Forms![Project] & " LF")
    objXLBook.SaveAs ("D:\Clients\Folder_Name\" & Me.Job_No & " " & Forms![Project] & " LF Report.xls")


    I changed the MkDir lines to match. This saves the excel file into the folder, however the folder is called "Folder_Name".
    Please help I am at a loss, and thank you for your time.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    14
    Rep Power
    0
    In order to use the string previously loaded into Folder_Name the line needs to be :

    objXLBook.SaveAs ("D:\Clients\" & Folder_Name & "\" & Me.Job_No & " " & Forms![Project] & " LF Report.xls")

    Hope that helps!

    Comments on this post

    • medialint agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    23
    Rep Power
    0
    its helps or not ?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Yes this worked. Thanks you very much for the help> I new it was something simple like that. I just need to learn how to properly write the codes I guess.

IMN logo majestic logo threadwatch logo seochat tools logo