Results 1 to 2 of 2
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    Importing Excel files using Access

    I am having a problem with making some changes to an existing master copy in Excel (xlsm) that is fed new information/ My main problem is when I use a module in Access to manipulate the table using vba to bring it into a new master copy (xls). The new information is not copied over correctly. I believe the problem could have to do with the connection properties of the xlsm copy. The connection does not behave the same when I changes some headings and change the file names of the connections. I have included a windows file showing steps and what they look like. Here is the code.

    ExcelIssue.zip

    Dim DistVar, StoreVar, LastRowVar, StoreFileLastRowVar, Dist2Var, DateXVar
    Dim DateVar As Date
    Dim fso
    Dim fol As String
    Dim folder, FileName, stDocName As String
    Sub Sync()
    ActiveWorkbook.RefreshAll
    MsgBox "Data Sync to data base."
    End Sub
    Sub Districts()
    'CREATE A FOLDER FOR THIS WEEK'S REQUESTS
    folder = "K:\Departments\Shipping\Eric\StoreSupplyProject\A pprovalFiles\test"
    Range("D3").Select
    DateVar = Selection
    DateXVar = Format(DateVar, "yyyy_mm_dd")
    fol = folder & DateXVar
    Call CreateFolder
    'CREATE AN APPROVAL FOLDER
    'fol = folder & DateXVar & "\ApprovedFiles"
    'Call CreateFolder
    'CAPTURE THE FIRST DISTRICT NUMBER
    Range("B3").Select
    DistVar = Selection
    'START LOOPING THROUGH THE DIFFERENT DISTRICTS
    Do While DistVar <> ""

    'MAKE A COPY OF THE BLANK FILE - SAVE AS DISTRICT
    FileCopy "K:\Departments\Shipping\Eric\StoreSupplyProject\A pprovalFiles\test\BlankDistrictFile.xlsx", _
    "K:\Departments\Shipping\Eric\StoreSupplyProject\A pprovalFiles\test" & DateXVar & "\District_" & DistVar & "_" & Format(Date, "mm-dd-yy") & ".xlsx"

    ChDir "K:\Departments\Shipping\Eric\StoreSupplyProject\A pprovalFiles\test"
    Workbooks.Open FileName:= _
    "K:\Departments\Shipping\Eric\StoreSupplyProject\A pprovalFiles\test" & DateXVar & "\District_" & DistVar & "_" & Format(Date, "mm-dd-yy") & ".xlsx"

    Call District

    'PLACE COPY ON THE G:\
    FileCopy "K:\Departments\Shipping\Eric\StoreSupplyProject\A pprovalFiles" & DateXVar & "\District_" & Dist2Var & "_" & Format(Date, "mm-dd-yy") & ".xlsx", _
    "G:\Teams and Projects\DSM Approval-Store Supply Orders" & Format(DateVar, "mm-dd-yy") & "\District_" & Dist2Var & "_" & Format(Date, "mm-dd-yy") & ".xlsx"

    'RESET FOR NEXT DISTRICT
    Windows("MasterDistrictFile.xlsm").Activate


    Range("B3").Select
    DistVar = Selection

    Loop
    'CLOSE MASTER FILE
    ActiveWorkbook.Close
    End Sub
    Sub District()
    Windows("MasterDistrictFile.xlsm").Activate
    'Range("C3").Select
    'StoreVar = Selection
    Range("B3").Select
    DistVar = Selection
    Range("B3").Select
    Dist2Var = Selection
    'START LOOPING THROUGH THE DIFFERENT STORES
    Do While DistVar = Dist2Var And DistVar <> ""

    'Filter master to next district data
    Windows("MasterDistrictFile.xlsm").Activate
    ActiveSheet.ListObjects("Table_StoreSupply").Range .AutoFilter Field:=2, Criteria1:=DistVar

    'Capture last district row in master
    Range("C3").Select
    Selection.End(xlDown).Select
    LastRowVar = Selection.Row

    'Copy store data
    Range("A3:S" & LastRowVar).Select
    Selection.Copy

    'Paste store data
    Windows("District_" & DistVar & "_" & Format(Date, "mm-dd-yy") & ".xlsx").Activate
    Range("S6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    'capture last row on store worksheet
    Range("W6").Select
    Selection.End(xlDown).Select
    StoreFileLastRowVar = Selection.Row

    'delete un-needed rows
    Range(StoreFileLastRowVar + 1 & ":50004").Select
    Selection.Delete Shift:=xlUp

    'copy values
    Range("A4:I" & StoreFileLastRowVar + 2).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("L4:Q" & StoreFileLastRowVar + 2).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    'unlock the approved units column
    Range("I5:J" & StoreFileLastRowVar).Select
    Selection.Locked = False

    'Delete columns with data
    Columns("S:AJ").Select
    Selection.Delete

    'Hide ID column
    Columns("S:S").Select
    Selection.EntireColumn.Hidden = True

    'set default view status
    Range("A6").Select
    ActiveWindow.FreezePanes = True
    Range("A5:Q" & StoreFileLastRowVar).Select
    Selection.AutoFilter
    Range("G6").Select

    'Delete store rows from master
    Windows("MasterDistrictFile.xlsm").Activate
    Range("3:" & LastRowVar).Select
    Selection.Delete Shift:=xlUp

    'Reset for next store
    ActiveSheet.ListObjects("Table_StoreSupply").Range .AutoFilter Field:=2
    Range("C3").Select
    StoreVar = Selection

    Dist2Var = DistVar

    Range("B3").Select
    DistVar = Selection

    Loop
    'Lock District file
    Windows("District_" & Dist2Var & "_" & Format(Date, "mm-dd-yy") & ".xlsx").Activate
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
    'Save District file
    ActiveWorkbook.Save
    'Email District file
    ActiveWorkbook.SendMail Recipients:="#District" & Dist2Var & "Mgmt", Subject:="District " & Dist2Var & " Supply Order - " & Format(Date, "m/d/yyyy")
    'Close District file
    ActiveWorkbook.Close
    End Sub

    Sub CreateFolder()
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(fol) Then
    fso.CreateFolder (fol)
    End If

    End Sub

    Thanks for taking the time to go through this.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see nothing that pertains to Access, only Excel.
    Is there an Access question?

    You might more help in an Excel forum. Try http://www.mrexcel.com/forum/index.php

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  2. Importing from several Excel files to Access.
    By cmdteardrops in forum Access
    Replies: 2
    Last Post: 10-02-2013, 03:33 PM
  3. regarding choking of access while importing excel files
    By ritimajain in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2013, 02:16 PM
  4. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  5. Replies: 6
    Last Post: 12-03-2012, 08:08 AM

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