Databases
StackOverflow2013
Postsdbo
POAutomating open Excel file/Run Script/Then Save Process with a VBA Script
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAutomating open Excel file/Run Script/Then Save Process with a VBA Script
    primarykey
    data
    text
    <p>I'm trying to build a database in Access by importing and appending hundreds of Excel documents in a certain folder together. Each imported excel spreadsheet needs to be basically uniform if it is to be appended correctly to the last excel spreadsheet in Access. In addition, blank spaces in the cells cause problems in access... Since there are hundreds of excel files to be added to Access, I wished to use VBA to automate the process... so here's what I'd like to accomplish:</p> <p>1st) The macro first scans through the folder with all Excel spreadsheets I wish to import... and automatically opens a single excel file at a time. 2nd) Checks that excel file to see that all blank spaces are filled with " - " 3rd) When it is, save that updated excel copy to a folder I name "New Project" 4th) repeat process on the next spreadsheet</p> <p>Here's the code I've written so far.. but haven't been able to have it Automatically open each file I need from a particular folder, run the rest of the script, then save it... </p> <pre><code> Sub Formatting() Dim counter As Integer Dim TotalFiles As Integer TotalFiles = 1 **'Loop through each xl file in a folder** For counter = 1 To TotalFiles **'Open multiple Files----------------------------------------------------------------------------------------------** Dim Filter As String, Title As String, msg As String Dim i As Integer, FilterIndex As Integer Dim xlFile As Variant Filter = "Excel Files (*.xls), *.xls," &amp; "Text Files (*.txt), *.txt," &amp; "All files (*.*), *.*" **'Default filter = *.*** FilterIndex = 3 **'Set dialog caption** Title = "Select File(s) to Open" **'Select Start and Drive path** ChDrive ("C") ChDir ("C:\Users\DTurcotte\Desktop\Test_Origin") With Application **'Set file name array to selected files (allow multiple)** xlFile = .GetOpenFilename(Filter, FilterIndex, Title, , True) **'Reset Start Drive/Path** ChDrive (Left(.DefaultFilePath, 1)) ChDir (.DefaultFilePath) End With **'Exit on Cancel** If Not IsArray(xlFile) Then MsgBox "No file was selected." Exit Sub End If **'Open Files** For i = LBound(xlFile) To UBound(xlFile) msg = msg &amp; xlFile(i) &amp; vbCrLf Workbooks.Open xlFile(i) Next i MsgBox msg, vbInformation, "Files Opened" **'Format Column Headings----------------------------------------------------------------------------------------------** ActiveWorkbook.Sheets.Select Dim RowIndex As Integer Dim ColIndex As Integer Dim totalRows As Integer Dim totalCols As Integer Dim LastRow As Long Dim range As range totalRows = Application.WorksheetFunction.CountA(Columns(1)) If Cells(1, 1).Value &lt;&gt; "ROOM #" Then Cells(1, 1).Value = "ROOM #" If Cells(1, 2).Value &lt;&gt; "ROOM NAME" Then Cells(1, 2).Value = "ROOM NAME" If Cells(1, 3).Value &lt;&gt; "HOMOGENEOUS AREA" Then Cells(1, 3).Value = "HOMOGENEOUS AREA" If Cells(1, 4).Value &lt;&gt; "SUSPECT MATERIAL DESCRIPTION" Then Cells(1, 4).Value = "SUSPECT MATERIAL DESCRIPTION" If Cells(1, 5).Value &lt;&gt; "ASBESTOS CONTENT (%)" Then Cells(1, 5).Value = "ASBESTOS CONTENT (%)" If Cells(1, 6).Value &lt;&gt; "CONDITION" Then Cells(1, 6).Value = "CONDITION" If Cells(1, 7).Value &lt;&gt; "FLOORING (SF)" Then Cells(1, 7).Value = "FLOORING (SF)" If Cells(1, 8).Value &lt;&gt; "CEILING (SF)" Then Cells(1, 8).Value = "CEILING (SF)" If Cells(1, 9).Value &lt;&gt; "WALLS (SF)" Then Cells(1, 9).Value = "WALLS (SF)" If Cells(1, 10).Value &lt;&gt; "PIPE INSULATION (LF)" Then Cells(1, 10).Value = "PIPE INSULATION (LF)" If Cells(1, 11).Value &lt;&gt; "PIPE FITTING INSULATION (EA)" Then Cells(1, 11).Value = "PIPE FITTING INSULATION (EA)" If Cells(1, 12).Value &lt;&gt; "DUCT INSULATION (SF)" Then Cells(1, 12).Value = "DUCT INSULATION (SF)" If Cells(1, 13).Value &lt;&gt; "EQUIPMENT INSULATION (SF)" Then Cells(1, 13).Value = "EQUIPMENT INSULATION (SF)" If Cells(1, 14).Value &lt;&gt; "MISC. (SF)" Then Cells(1, 14).Value = "MISC. (SF)" If Cells(1, 15).Value &lt;&gt; "MISC. (LF)" Then Cells(1, 15).Value = "MISC. (LF)" **'Fills in blank spaces with "-"** For RowIndex = 1 To totalRows For ColIndex = 1 To 15 If Cells(RowIndex, ColIndex).Value = "" Then Cells(RowIndex, ColIndex).Value = "test" Next ColIndex Next RowIndex **'Clears content from "Totals" Row** With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Rows(LastRow).ClearContents **'Saves file to a new folder 'Need to have the code run through that excel doc, set that updated copy to a variable, and then have the following code save it to a new folder** ***ToDo*** **'newSaveName = updated excel file** 'ActiveWorkbook.SaveAs ("C:\Users\DTurcotte\Desktop\TestExcelFiles" &amp; Test1_Success &amp; ".xls") Next counter End Sub </code></pre> <hr> <p>Can anyone provide any help?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USGrowler
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAutomating open Excel file/Run Script/Then Save Process with a VBA Script
      1. USOlegarioFuentes
      1. VTFavorite
    1. COThe curly braces {} symbol on the toolbar allows you to format code when you are posting, it will make your post more readable and more likely to get an answer.
      singulars
      1. POAutomating open Excel file/Run Script/Then Save Process with a VBA Script
      1. USFionnuala
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload