Import VBA assistance

[skinny220]

All,

Does anyone know a VBA that will import three files (Inventory.xlxs, Serial Number.xlsx, Quote.xlsx.) all files are in a folder I have labeled Data Import?

Skinny (Carter)

 

[ParamRay]

.
.

Perhaps something like this:

Sub ImportData()
  Const strDIR As String = "C:\Data Import\"
  Dim varFnames As Variant
  Dim wbkSource As Workbook
  Dim wbkDestin As Workbook
  Dim intWIndex As Integer
  Dim wksSource As Worksheet
  
  varFnames = Array("Inventory.xlsx", "Serial Number.xlsx", "Quote.xlsx")
  Application.DisplayAlerts = False
  Set wbkDestin = Workbooks.Add
  For intWIndex = LBound(varFnames) To UBound(varFnames)
    If Dir$(strDIR & varFnames(intWIndex)) <> vbNullString Then
      Set wbkSource = Workbooks.Open(strDIR & varFnames(intWIndex))
      For Each wksSource In wbkSource.Worksheets
        wksSource.UsedRange.Copy
        With wbkDestin.Sheets(Sheets.Count)
          .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
          .Name = Split(wbkSource.Name, ".")(0) & Chr$(95) & wksSource.Index
        End With
        wbkDestin.Sheets.Add
      Next wksSource
      wbkSource.Close False
    End If
  Next intWIndex
  Application.DisplayAlerts = True
      
End Sub

 

[skinny220]

Thanks a million.

 

[alansidman]

skinny220:

You posted in the Access forum. Were you looking to import the Excel files into Access?