Can I import excel data without opening the workbook???

[ooch1]

Hello,

I am trying to import some data from other files and accordingly paste the data into a specific excel doc, so i can run additional flagging code against it.

To start with i have just recorded my actions, however i am sure that this code can be reduced.

My first question is is the first line of code (chdir...) necessary, or will the second line work ok on it's own?

Is it absolutely necessary to physically open the other document, as then i have to skip between docs all the time to get back to where i want to past the data and if i happen to have any other excel docs open at the time then this could cause me problems? I have already been given a more consise bit of copy - destination:= code which i think i can use, but i will still have to open & close all the docs every time.

My recorded code is as below:

Sub OPenFiles()
    ChDir "D:\02-EXCEL_DOCs"
    Workbooks.Open Filename:="D:\02-   EXCEL_DOCs\Gas_Aston_COT_PortAnlys.csv"
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:BA3435").Select
    Selection.Copy
       ActiveWindow.ActivatePrevious
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Range("A3436").Select
End Sub

OOch

 

[VBAjedi]

The short answer is yes, you do need to open the source workbook. I have seen some attempts to get information out of closed workbooks, but they are generally clunky because Excel isn't really designed for that. . .

I find challenges like this much easier to deal with if I use worksheet variables. Have a look at the following modification to your code:

Sub OpenFiles()
    Dim WB1 As Workbook, WB2 As Workbook
    Set WB1 = ActiveWorkbook
    SourceFilePath = "C:\My Documents\Temp\TB1.xls"
    Set WB2 = Workbooks.Open(FileName:=SourceFilePath)
    Range("SourceRange1").Copy 'A2:BA3435
    WB1.Activate
    ActiveSheet.Paste
    WB2.Close
End Sub

To me, that's more readable (and a bit shorter to boot!). Note that I also got rid of all the selects - you almost never need them when coding in VBA, even though the macro recorder puts them in.

Let me know if that works for you!

VBAjedi

 

[ooch1]

VBAJedi

Thanks you for your help, the only problem is the variable sourcefilepath needs to be defined and i'm not sure what it should be defined as?

OOch

 

[HughLerwill]

You may consider using QueryTable to get your csvs into a worksheet like this;

With Workbooks("DestBook").Sheets("DestSheet")
With .QueryTables.Add(Connection:="TEXT;" & "Path2~My.csv", Destination:=.Range("A1"))

'remmed properties are not required or have satisfactory values set by default
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
'.PreserveFormatting = True
'.RefreshOnFileOpen = False
'.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
'.RefreshPeriod = 0
'.TextFilePromptOnRefresh = False
'.TextFileStartRow = 1
'.TextFileConsecutiveDelimiter = False
'.TextFileSemicolonDelimiter = False
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True 'only good in XP and later


.Name = "My"
.AdjustColumnWidth = False
.TextFilePlatform = xlWindows
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileTabDelimiter = dpChar() <> "."
.TextFileCommaDelimiter = dpChar() = "."
.Refresh BackgroundQuery:=False
.Delete 'delete connection to file, data remains in the sheet
End With
End With

If the dest sheet is formatted (eg. a template) the imported data is formatted accordingly.

This may work since Excel 97 but I have only got it to work in Excel 2000 and XP. Using this method is about 10 times faster than opening a new workbook for each csv and then copying its contents to the dest book.
Hope this helps

Hugh

 

[VBAjedi]

Oh, sorry! You must be using Option Explicit to force declaration of all variables. (Good for you - I'm just too lazy to do so!).

SourceFilePath is just a String variable. If the file is always in the same place, you can just hardcode the path. If you would rather prompt the user for the filepath, use something like:

Dim SourceFilePath As String
Do
   SourceFilePath = Application.GetOpenFilename _
   ("Microsoft Excel Workbook (*.xls),*.xls")
   If SourceFilePath = "False" Then
      x = MsgBox("No file specified. Try again?", vbOKCancel)
      If x = 2 Then Exit Sub
   End If
Loop While SourceFilePath = "False"

which will invoke the "Open File" dialog and return the path to the selected file as a string.

VBAjedi

 

[SkipVought]

You can also use Data/GetExternal Data/New database Query....

to get data out of another workbook,as long as the data is a table, each table on a separate sheet.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}