Page 1 of 5 12345 LastLast
Results 1 to 15 of 66
  1. #1
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88

    Exclamation Need Code to Automate ImportExportSpreadsheets

    Hello Everyone,



    I'm super new to VBA and Access automation tools. I've done more of database planning, theory, creation, and reporting.

    This is my first time in an open source forum!!

    Anyhow, any help would be super appreciated.

    Objective:
    Automate ImportExportSpreadsheets
    Vision: button that when clicks prompts user to enter file path, file name, and adds .xlsx
    This table will be a template and the users will paste the information they'd like to append to specific datatable in Access.
    I can have it matching the table to be imported to, but there are 2 columns that would be more clear for the users if their titles differed.
    The users don't need to know which table they're appending to, just that their information is being added.

    The macro should append the Excel Sheet to the table I've defined. I'd then want the table the user has appended to not be in the database, it would be even better if Access could clear the table in Excel after as well.

    Attempts:
    I've tried looking for code, but I just don't understand it well enough since the variables are usually tailored to specific cases.
    My original goal was to use the Macro ImportExportSpreadSheet and to transfer it to VBA code, but that doesn't seem to be working.
    I also continuously get an error telling me that F1 is not found in my destination table in Access. I've also researched various solutions and nothing has seemed to be working.
    I've double check the columns, formatting, names of all paths, files, extensions, I tried the .csv file, etc.
    I did eventually get the F1 to go away for a little while, but when I tried to transfer it to VBA, it told me it was successful, but the code just pointed to a failed attempt.

    This is a process I'll need to repeat with 5 other tables, so it's really important. After struggling, all that comes to my head is, it's not that hard without the automation, they can figure it out haha.
    Anyways, thanks in advance for any help you guys can give me

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The simplest way to import a spreadsheet is to use the TransferSpreadsheet method

    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "TableName", "FileName", True

    You can place this line of code behind a button's click event.

    You can implement variables and make your code dynamic with a couple of string variables.

    Dim strTable as string
    dim strFilePath as string
    strTable = "MyTableName"
    strFilePath = "C:\Test\FileName.xlsx"
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", True

    The above is still hardcoded with literal text but the declaration of the two string variables give you a couple of buckets to hold User Input.

    You can collect user input via the File System Object's FilePicker. You need to make a reference to Microsoft Office XX.X Object Library in order for (Application.FileDialog) to work.



    Code:
    Dim strTable as string
    dim strFilePath as string
    
    With Application.FileDialog(msoFileDialogFilePicker)
    
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "Excel", "*.xlsx"
        .InitialFileName = "C:\Test"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
            'There is a problem
            Exit Sub
            End If
        
        'Save the first file selected
        strFilePath = Trim(.SelectedItems(1))
        
    End With
    
    strTable = "MyTableName"
    
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", True

  3. #3
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Great, Thank you sooo much for responding and so quickly.
    I actually understand what you're doing to some degree. I'm taking a Lynda.com VBA course
    But I'm not sure how to make the relationship to Microsoft Office XX.X Object Library in order for (Application.FileDialog)

    Also, I'm not entirely clear on the strFilePath though.
    It is the FilePath + "\" + File Name + ".xlsx" + SpreadsheetName

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can reference a library throught the VBA Editor. Open the references window via the Tools pulldown in the Menu Bar.

    Within the VBA editor, Tools>References

    .
    Click image for larger version. 

Name:	ReferencesWindow.jpg 
Views:	66 
Size:	62.7 KB 
ID:	16658

    Tick the box next to the reverence library desired and click OK

    Also, I'm not entirely clear on the strFilePath though.
    One of the arguments for the TransferSpreadsheet method is FileName. This is requesting the full path to the file. The argument is passed to the method as a string. So, yes, you need the drive, folders, file name, extension.
    Code:
    strFilePath = "C:\Test\FileName.xlsx"
    I would break this off into small bits and get the import process down first. Perhaps using the Wizard to import the spreadsheet will help. The wizard can help to create a new table that you can then use as your table name in the TransferSpreadsheet method.

  5. #5
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Ok, I check the reference window and was unable to find the reverence library. I have the same items checked as you and I'm getting the same error: compile error: invalid outside procedure.

    I mean I'm pretty clear about what I need for my process:

    Ask user for file path
    Ask user for file name
    Ask user for file spreadsheet name (I have multiple sheets, this is the range function)

    importTable construction = file path, "\", file name, ".xlsx", true, "\", file spreadsheet name, "!"

    destinationTable (has already been created)

    transfer (need to make sure this is an append function) importTable data to destinationTable

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    "invalid outside procedure" is not a reference issue. When you click debug, what line of code is highlighted. Can you post all of your code here? Use the Code brackets (Hashtag Icon #), which is available after clicking "Go Advanced", when posting code to the forum.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You don't actually have to rely on the user to enter all that information you can use common dialogue boxes or you can create your own list of files within a folder and use code to tell you what files are available in that folder. Here's an example.

    EDIT
    OOPS forgot to attach....
    Ace2014.zip

    EDIT 2
    You'll need to have the reference Microsoft Office xx.x Object Library (I made the example with access 2013 so my reference is 15.0 object library)

  8. #8
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Code:
    Option Compare Database
    
    
    
    Dim strTable As String
    Dim strFilePath As String
    
    
    With Application.FileDialog(msoFileDialogFilePicker)
    
    
        .AllowMultiSelect = False
        .Title = "Locate a Excel file to Import"
        .ButtonName = "Market Data"
        .Filters.Clear
        .Filters.Add "Excel", "*.xlsx"
        .InitialFileName = "C:\Test"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
            'There is a problem
            Exit Sub
            End If
        
        'Save the first file selected
        strFilePath = Trim(.SelectedItems(1))
        
    End With
    
    
    strTable = "tbl1_PartPricingDataAggregation"
    
    
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", TrueSub Import()
    
    
    End Sub
    Sub Import()
    
    
    End Sub

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You did not say which line is throwing the exception. However, this looks to be out of place
    strFilePath", TrueSub Import()

    as well as this

    Code:
    End Sub
    Sub Import()
    
    
    End Sub
    Make sure your code goes between the
    Sub Import()

    and
    End Sub

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    where is that code crashing I'm assuming it's on the transferspreadsheet line and likely due to the 'truesub import'

    I'm guessing that should say TRUE and remove the SUB IMPORT() from the end of that line since you have a 'sub import() two lines below that.

  11. #11
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    sorry, it's highlighting this part
    With Application.FileDialog(msoFileDialogFilePicker)

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Ace2014 View Post
    sorry, it's highlighting this part
    With Application.FileDialog(msoFileDialogFilePicker)
    Normaly, I would say that is a reference issue. However, the issue is that you cannot have anything that is not notes or declarations in the header area. You pasted code outside of the Sub Procedure, just after "Option Compare Database" and before "Sub Import()"


    So
    Dim strTable As String
    Dim strFilePath As String

    are OK to have in the header but
    With Application.FileDialog(msoFileDialogFilePicker)

    and the rest is not OK to have outside of a Procedure or function

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Have you added the reference to Microsoft Office xx.x Object Library.

  14. #14
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Yeah, I know, Access wouldn't let me take it out of the header area.
    I tried to cut and paste below, but each time the line moves to the end just above "Sub Import()"

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Ace2014 View Post
    Yeah, I know, Access wouldn't let me take it out of the header area.
    I tried to cut and paste below, but each time the line moves to the end just above "Sub Import()"
    try
    Copy the code from the internet and paste into Notepad. Select all from Notepad and Copy. Then paste that into your Sub Procedure.

    Not sure why you are having this trouble. If you can (if there is not a lot of other code in your form's module) remove all of the code from your module (except for "Option Compare Database") and then create a new event handler before pasting.

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 01-30-2014, 05:26 PM
  2. Replies: 11
    Last Post: 12-11-2013, 10:35 AM
  3. Automate Attendance
    By Palomino33 in forum Access
    Replies: 3
    Last Post: 01-14-2012, 03:57 PM
  4. Automate....
    By kusamharsha in forum Queries
    Replies: 3
    Last Post: 05-14-2009, 03:02 AM
  5. Replies: 1
    Last Post: 09-06-2006, 11:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums